On Saturday 02 May 2009 06:34:57 pm Craig Ringer wrote: > johnf wrote: > > I have a list (or array) of primary keys stored in a field (data type > > text). I would like to use the list to retrieve all the data from a table > > based on the list. > > > > my text field contains: > > '123,134,343,345' > > > > I would to do something like the following: > > > > Select * from table1 where table1.pkid in (select myTextfield from > > table2) > > > > So the question is there a simple way to use the list to retrieve my > > data? > > http://www.postgresql.org/docs/8.3/static/functions-matching.html > > SELECT * FROM table1 > WHERE table1.pkid IN ( > SELECT x FROM regexp_split_to_table(myTextfield, ',') AS x > ); > > ... but you should consider storing your list in an array instead, or > using a more conventional child table with a (pkid, refid) pair list. > > -- > Craig Ringer
Thanks - I think this will work very well. I considered an array but at the moment I don't have an easy way of retrieving data from an array. I'm working on that as I type. The other solution would be a table but that seems over kill for one field. Thanks again -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
