Declare a variable in RedShift

Slavik Meltser's answer is great. As a variation on this theme, you can also use a WITH construct:

WITH tmp_variables AS (
SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id
)

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

Actually, you can simulate a variable using a temporarily table, create one, set data and you are good to go.

Something like this:

CREATE TEMP TABLE tmp_variables AS SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id;

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);

The temp table will be deleted after the transaction execution.
Temp tables are bound per session (connect), therefor cannot be shared across sessions.

No, Amazon Redshift does not have the concept of variables. Redshift presents itself as PostgreSQL, but is highly modified.

There was mention of User Defined Functions at the 2014 AWS re:Invent conference, which might meet some of your needs.

Update in 2016: Scalar User Defined Functions can perform computations but cannot act as stored variables.

Declare a variable in RedShift

Related

Recent Posts