PostgreSQL: How to search a list of strings as a table?

postgresql, sql

Solution

You don't need to mess around with arrays at all, you can build the table in-place using VALUES:

7.7. VALUES Lists

`VALUES` provides a way to generate a "constant table" that can be used in a query without having to actually create and populate a table on-disk.

See also VALUES.

So you can do things like this:

=> select *
   from (
       values ('1', 'a', 'A'),
              ('2', 'b', 'B'),
              ('3', 'c', 'C')
    ) as t(id, c1, c2)
    where id = '2';

 id | c1 | c2 
----+----+----
 2  | b  | B
(1 row)

Don't forget to give your VALUES an alias complete with column names (`t(id, c1, c2)`) so that everything has a name.

Problem

It's been a while since I've had to do any db work, so I'm not really sure how to ask this and I know I've done it in the past. How do you create a temporary table out of a list of strings (not using CREATE TEMPORARY TABLE)? So, if you have something like : '1', 'a', 'A' '2', 'b', 'B' '3', 'c', 'C' ``` SELECT field2 FROM { {'1','a','A'}, {'2','b','B'}, {'3','c','C'} } AS fooarray(field1,field2,field3) WHERE field1 = '2' -- should return 'b' ``` Hint: It's similar to... ``` SELECT * FROM unnest(array[...]); ```

Original source