On Tue, May 26, 2009 at 11:04 PM, Nick <nboutelier@gmail.com> wrote:
> I wont go into details about why im using this field as an array but
> how would I select all the rows that have the first name 'Tom' out of
> the 'names' field?
>
> CREATE TABLE test (
> id integer,
> names character varying[]
> );
> INSERT INTO test VALUES (1, '{"''Josh Berkus''","''Peter
> Eisentraut''","''Marc Fournier''"}');
> INSERT INTO test VALUES (2, '{"''Tom Lane''","''Bruce
> Momjian''","''Dave Page''"}');
> INSERT INTO test VALUES (3, '{"''Jan Wieck''","''Oleg
> Bartunov''","''Joe Conway''"}');
couple of ways:
select * from test where 'Jan Wieck' = any(names);
on 8.4:
select * from (select unnest(names) as n from test) q where n = 'Jan Wieck';
while the second approach seems more complex, it's a more general type
of thing that can be used to attack all kinds of problems. Previous
to 8.4 you have to write your own unnest (it's not hard) or use the
built in information_schema._pg_expandarray();
merlin