How to select multiple rows filled with constants in Amazon Redshift?

sql postgresql amazon-redshift set-returning-functions
Inferno1892 picture Inferno1892 · Jan 30, 2015 · Viewed 7.5k times · Source

I have already tried the common PostgreSQL answer, but seems like it doesn't 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 can't use UNION ALL. Any help will be greatly appreciated.

Answer

Erwin Brandstetter picture Erwin Brandstetter · Mar 11, 2015

The correct Postgres syntax would be:

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

A set of parentheses was missing.
But it seems Redshift does not even support a VALUES expression outside of INSERT (like modern Postgres does). So, for a single row:

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

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

A temporary table is automatically dropped 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;

Related questions

postgresql - view schema privileges

Is there a query I can run to show currently assigned privileges on a particular schema? i.e. privileges that were assigned like so: GRANT USAGE ON SCHEMA dbo TO MyUser I have tried SELECT * FROM information_schema.usage_privileges; …

Read More
How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using to_date("act_dt",'YYYYMMDD') AS "act date" …

Read More
JOIN (SELECT ... ) ue ON 1=1?

I am reading an SQL query in Redshift and can't understand the last part: ... LEFT JOIN (SELECT MIN(modified) AS first_modified FROM user) ue ON 1=1 What does ON 1=1 mean here?

Read More