CODEWITHSUNDEEP
postgresql
Richard Knop
Richard Knop

Reputation: 83725

Select hardcoded values without table

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.

Upvotes: 144

Views: 96699

Answers (4)

Arun Kottayil
Arun Kottayil

Reputation: 21

The other answers using 'values' is a more robust solution. However, if you have a list of single values already available as comma separated string, in Postgres you can also input the list as an ARRAY and use unnest to convert to multiple rows.

SELECT unnest(ARRAY[10,20]) as val;
select unnest(ARRAY['val1','val2']) as val;

sql result

Upvotes: 2

Vivek S.
Vivek S.

Reputation: 21915

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

Upvotes: 78

Solanki Vaibhav
Solanki Vaibhav

Reputation: 494

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

Upvotes: 12

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125414

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

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

Upvotes: 197

Related Questions