Variables in Redshift

Question

I would like to have a variable in my query.

CREATE TEMP TABLE tmp_date_var AS SELECT 
   '2023-02-21'::DATE AS TargetDate;

and then I used it in my (long) query where TargetDate appears quite often.

The first run was successful, then I changed the TargetDate to be '2023-02-20', I received an Error: "ERROR: relation "tmp_date_var" already exists" and the TargetDate remains '2023-02-21'.

How can I solve it?

Thanks a lot in advance!

CREATE TEMP TABLE tmp_date_var AS SELECT '2023-02-21'::DATE AS TargetDate;

score 0 · Answer 1 · answered Feb 22 '23 at 09:26

0

There is no concept of a 'variable' in Amazon Redshift.

The error you received is because you attempted to create another table with the same name.

Some SQL Clients (eg DBeaver) allow you to put ? placeholders in your SQL. The client will then prompt you for a value when the SQL query is run. Amazon Redshift receives the query with the variable substituted in-place. Thus, it is the SQL Client that can handle variables for you, rather than the database engine (Amazon Redshift in your case).

answered Feb 22 '23 at 09:26

John Rotenstein

241,921
22
380
470

Thanks for your answer John. Do I get you right, there is no chance to build in variables directly in RedShift? – SQL_NewBe Feb 22 '23 at 09:45
That is correct. – John Rotenstein Feb 22 '23 at 09:59

score 0 · Answer 2 · answered Feb 22 '23 at 16:06

As you have been informed there are no variables in Redshift but some benches have the ability to variable substitution before SQL is sent to the database.

To resolve your temp table error you can just DROP the table before you re-CREATE it. Just always make sure that the temp table doesn't exist before setting the value to it:

DROP TABLE IF EXISTS tmp_date_var; CREATE TEMP TABLE tmp_date_var AS SELECT '2023-02-21'::DATE AS TargetDate;

Run these 2 statement instead one just 1 and you can change the temp table contents as you like.

score 0 · Accepted Answer · answered Feb 22 '23 at 22:36

Redshift does not support variables. If your use case is that you want to use one particular value in multiple subqueries, the easiest option is to use a Common Table Expression such as

with myvariables as (
    SELECT '2023-02-21'::DATE AS TargetDate
)
select *
from mytable
where targetdate = (select TargetDate from myvariables)

Variables in Redshift

3 Answers3