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

Question

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.

Valenti

Just ran into this myself. Here are the docs on which Postgres features are unsupported in Redshift: docs.aws.amazon.com/en_pv/redshift/latest/dg/… – Tarshatarshish

Answer 1

The correct Postgres syntax would be:

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

A set of parentheses was missing.

But Redshift does not support free-standing VALUES expressions (outside of INSERT commands). 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:

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

SELECT * FROM q;

The manual:

A temporary table is automatically dropped at the end of the session in which it was created.

If UNION ALL is an option:

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

Materfamilias