Redshift Temporary Tables, Usage and Examples

Similar to many other relational databases such as Netezza, Snowflake, Oracle, etc. Amazon Redshift support creating temp or temporary tables to hold non-permanent data. i.e. Data which you will use only in the current session and Redshift will drop the temp table soon after the session ends. In this article, we will check how to create Redshift temp or temporary tables, syntax, usage and restrictions with some examples.

Redshift Temporary Tables

The temporary table in Redshift is visible only within the current session. The table is automatically dropped at the end of the session in which it is created and it is not recoverable by any means. The temporary table is created in a separate, session-specific Redshift schema. However, you cannot specify a name for this schema. The schema is internal and not visible to the user.

Redshift Create Temporary Table Syntax

To create a temporary table, simply specify the TEMPORARY keyword (or TEMP abbreviation) in your CREATE TABLE DDL statement.

For example,

create temporary table temptablename 
(
col1 type1, 
col2 type2, 
... 
coln typen,);

Redshift Temporary Table Example

Following is the example of create temp table in Amazon AWS Redshift.

dev=# create TEMPORARY table TEMP_TEST (col1 int, col2 int);
CREATE TABLE

Create Redshift Temporary Table with same name as Permanent Table

The temporary table can have the same name as a permanent table. The temporary schema in which temp table is created becomes the first schema in the search path, so the temporary table will take precedence over the permanent table unless you qualify the table name with the schema name to access the permanent table.

-- Create temp table
dev=# create TEMPORARY table TEST (col1 int, col2 int);
CREATE TABLE

-- -- Create permanent table
dev=# create table TEST (col1 int, col2 int);
CREATE TABLE

The Redshift Temp table will take precedence when you use the same table name. However, you can override this by qualifying the schema name with permanent table.

For example,

-- You should qualify schema name to access content of permanent tables
dev=# select * from public.test;
 col1 | col2
------+------
    1 |    1
    5 |    5
    7 |    7
(3 rows)

-- If no schema is mentioned, TEMP table content will be displayed.
dev=# select * from test;
 col1 | col2
------+------
    3 |    3
    1 |    1
    2 |    2
(3 rows)

Redshift Temporary Table Restrictions

Following are some if the restriction on the temp tables.

• Temporary tables are not visible to other users or sessions.
• Data is not recoverable. The Redshift will automatically drops the temporary table data at the end of the session.

Hope this helps 🙂