On Mittwoch 25 Februar 2009 Tony Liao wrote: > hi all, > I have a table table_A (id serial,prefix varchar),for example. > now I want to get the id of "johnsmith"'s prefix match > table_A.prefix,so I do select id from table_A where 'johnsmith' like > prefix||'%'
SELECT id FROM table_A WHERE prefix LIKE 'johnsmith%';
in my table,the values of prefix are ('john','tom','anne','jim'......),so I don't think my SELECT was wrong. by the way,my postgres-server version is 8.36.thanks
> ,the table_A is very large so I would like to make > index. create table_A_index on table_A(prefix) > I try to explain analyze,but it doesn't work ,it use seq scan.
Because your SELECT was wrong.
> I try another index. drop index table_A_index; create > table_A_index on table_A(prefix varchar_pattern_ops); it doesn't > work,too. > thanks > ps:I have another table table_B would use table_B.prefix= > table_A.prefix.so how can I create the index?
That's a foreign key, if I understand you correctly.
ALTER TABLE ONLY B ADD CONSTRAINT B_prefix_fkey FOREIGN KEY (prefix) REFERENCES A(prefix) ON UPDATE CASCADE ON DELETE CASCADE;