Geek asks and answers

PostgreSQL: how to search a list of rows in a table?

Some time has passed since I had to do some work with db, so I'm not quite sure how to ask about it, and I know that I did this in the past.
How to create a temporary table from a list of rows (without 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 looks like ...

  • SELECT * FROM unnest(array[...]);
+5
source share
1 answer

You don’t have to bother with arrays at all, you can create a table in place using VALUES :

7.7. VALUES Lists

VALUES " ", .

. VALUES.

, :

=> 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)

VALUES (t(id, c1, c2)), .

+10

More articles:


All Articles