How to create and call temp table in redshift

Question

I want to create a temp table and call this temp table in redshift? I have tried this one but got an error message.

select  top 10 * into #a from public.item_activity where item_act_timestamp < DATEADD(day,-1,getdate());

select * from #a

"ERROR: 42P01: relation "#a" does not exist"

Answer 1

You can use

Create temporary table tbl_name as (Select * from abc)
--OR
create temporary table tmp_date_var AS
( SELECT '2017-04-01'::Date AS stdt,'2018-01-31'::Date AS enddt);

Answer 2

Please create table table using command below below:

  CREATE TEMP TABLE temp_table_name ....

Once this is done , use select query as below

  select * from temp_table_name

The temp table exists only for duration of your session using which you have created the table.

Answer 3

This problem vexed me for a while as well so posting solution even though the question was asked a while ago. The solution lies in the tool you're using, Aginity in my case, there are 2 ways to solve:

• Temporary solution for one query window and one query session: Click on Options-> Current Query Options -> Check box "Keep connection open between executions"
• More permanent solution for all query windows and all sessions: Click on Tools -> Options -> Query Analyzer -> Select "Keep connection open between executions" under Connection Handling

If you're not using Aginity, look for a similar setting in your tool.

Hopefully, I was able to help someone with this!