Question 18659 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Variables in Redshift – Amazon Web Sevices

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;

Tags:

3

Answers


  1. 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).

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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)

    See also Declare a variable in RedShift

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search