118

I need to run a select without actually connecting to any table. I just have a predefined hardcoded set of values I need to loop over:

foo
bar
fooBar

And I want to loop through those values. I can do:

select 'foo', 'bar', 'fooBar';

But this returns it as one row:

 ?column? | ?column? | ?column? 
----------+----------+----------
 foo      | bar      | fooBar
(1 row)

I am using Postgresql.

Richard Knop
  • 81,041
  • 149
  • 392
  • 552

3 Answers3

175
select a
from (
    values ('foo'), ('bar'), ('fooBar')
) s(a);

http://www.postgresql.org/docs/current/static/queries-values.html

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 6
    It is possible to write "AS s(a)" instead of "s(a)" which makes it more explicit that "s(a)" gives names to the hardcoded table, where the "a" is the column name. – peschü Dec 29 '19 at 11:25
  • @peschü - worked when I tried it. – Lee Goddard Aug 09 '22 at 09:19
  • 2
    Worked nicely, but what _is_ `s` here exactly and why does it know how to return the value instead of a tuple?? – Matheus Felipe Nov 30 '22 at 19:19
  • 1
    @MatheusFelipe `s` is the table name and `a` is the column name. So you could've written `select s.a` instead too. The parenthesis (`(a)`) isn't required, but then the column name gets a default name of `column1` instead (i.e., `select s.column1`). – Algorythm Mar 20 '23 at 17:24
54

To produce more than one column,

SELECT * 
FROM (VALUES ('Foo', 25), ('Bar', 30), ('Baz', 35)) AS t(name, age);

output

 name | age 
------+-----
 Foo  |  25
 Bar  |  30
 Baz  |  35

Using unnest()

Expand an array to a set of rows

select unnest(array['foo', 'bar', 'fooBar']);

demo

To produce more than one columns

SELECT *
FROM unnest(
            ARRAY['foo', 'bar', 'fooBar'] 
           ,ARRAY[25, 30, 35]
           ) AS t(name, AGE);

output:

 name  | age
-------+-----
 foo   |  25
 bar   |  30
 fooBar|  35
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • 2
    This works fine when there is only one value to be unnested, but quickly becomes unreadable when multiple have to be provided as you have to separate the values in many different unnest – Lucat Jul 11 '18 at 11:42
  • 3
    Most simple and complete syntax `SELECT x, 2 y FROM unnest('{1,2,3}'::int[]) t(x);` – Peter Krauss Jun 04 '20 at 12:43
  • @Luke using my illustrated `t(x)` and `t2(x)` etc. you can use LATERAL JOIN, CROSS JOIN, INTERSECTION, etc. and express datatypes. – Peter Krauss Jun 04 '20 at 12:46
12

Postgres SQL:

For static data output as single row and multiple columns representation use following query:

select a,b,c from (values('foo','bar','fooBar')) s(a,b,c);

result of this SQL query:

enter image description here

Solanki Vaibhav
  • 449
  • 6
  • 10