How do I select multiple rows filled with constants in Amazon Redshift?

Question

I already tried the generic PostgreSQL answer, but it doesn't seem to work with Redshift:

SELECT * FROM VALUES (1) AS q (col1); ERROR: 42883: function values(integer) does not exist 

I need this because for some reason I cannot use UNION ALL . Any help would be greatly appreciated.

Answer 1

The correct Postgres syntax is:

 SELECT * FROM ( VALUES (1) ) AS q (col1); 

Missing set of parentheses.
But it seems that Redshift does not even support the expression VALUES outside INSERT (as modern Postgres does). So for one line :

 SELECT * FROM ( SELECT 1 ) AS q (col1); 

For multiple rows (without using UNION ALL as requested) you can use a temporary table. Note ( for documentation ):

The temporary table is automatically discarded at the end of the session in which it was created.

 CREATE TEMP TABLE q(col1 int); INSERT INTO q(col1) VALUES (1), (2), (3); SELECT * FROM q; 

If UNION ALL would be an option:

 SELECT 1 AS col1 UNION ALL SELECT 2 UNION ALL SELECT 3;