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

Question

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[...]);
```

For 1, it's not working too well with multidimensional arrays. But let's just say that I have a one-dimensional array, I'm still struggling with joining on an existing table select lower(letter) from (select * from unnest('{"A","B","C"}') as letter) as foo where lower(letter) not in (select lower(letter) from someothertable); — vol7ron
– vol7ron, Commented Jun 5, 2012 at 18:26
Update: So I don't know what the joining problem was, but it is joining fine now. Still the multiple dimensions is somewhat of a problem. I wanted to use only native functions and avoid using any sort of stored proc to unnest the dimensions. -- mu's values is what I was looking for, and probably what I did in the past — vol7ron
– vol7ron, Commented Jun 5, 2012 at 19:57

mu is too short · Accepted Answer · 2012-06-05 19:01:22Z

19

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.

answered Jun 5, 2012 at 19:01

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vol7ron Over a year ago

For some reason I thought I was using the {{},{}} syntax, but then I remembered I used values as well. Either way, this is what I needed. Should you also specify the datatypes on the alias (eg t(id::numeric,c1::text,c2::text))?

mu is too short Over a year ago

@vol7ron: AFAIK you'd have to specify the data types inside each of the VALUES, you probably don't need them though, the types should be inferred just fine.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related