Overview

In this tutorial, we will learn about a PostgreSQL temporary table and its usage. We will study the basic syntax and some examples to understand its functionality.

Definition

A PostgreSQL temporary table, as its name implies, is a table that exists for a given session and gets automatically dropped when the session is closed. The temporary table is specific to a given session and cannot be accessed from another session. A query from another session returns an error if it tries to access a temporary table created by a different session.

A few key features Of  temporary tables:

• A temporary table is specific for a given session and is not accessible from another session
• A temporary table gets automatically dropped on closing the session.
• A temporary table does not belong to any schema.
• PostgreSQL allows a temporary table with the same name as that of a permanent table in the same database.
• When a temporary table shares the same name as that of a permanent table, the permanent table cannot be accessed until the temporary table gets dropped from the database or session.

Basic Syntax

CREATE TEMPORARY TABLE table_name(
   column_list
);
OR 
CREATE TEMP TABLE table_name(
   column_list
);

Both the keywords Temporary or Temp are supported in PostgreSQL and can be used interchangeably to create temporary table.

In this syntax:

• First, specify the name of the temporary table after the CREATE TEMPORARY TABLE keyword.
• Second, specify the column list for the table.

Now, let us study few examples to explain the above features.

Example 1 - Open a session, create a new temporary table in the default 'postgres' database.  Open another session in parallel and try to access the temporary table created in previous session. It is found that the table is not accessible.

CREATE TEMP TABLE mytable(
   name varchar,
   age integer
);

New session,

Example 2 - Temporary table gets automatically dropped on closing the session

First, we will create a new table in default 'postgres' database, then close the session and finally try to access it back after reopening a new session.

Opens a new session and finds the table doesn't exist. Thus proves the fact that the table ceases to exist outside the session in which it was created.

Example 3 - We create two tables, one temporary and one permanent having the same name. Post creation, we try to access the tables but find only the temporary table is accessible.

Create a table named 'students' and query it. At this point of time the behavior is as expected.

CREATE TABLE students(
   id SERIAL PRIMARY KEY, 
   name VARCHAR NOT NULL
);

Second, create a temporary table with the same name 'students' and again try to query it. At this juncture, it is observed the temporary table is given preference and returned ahead of the permanent one. Once again here, the important point to note is, the temporary table is session specific and is lost on closing the session unlike the permanent one.

CREATE TEMP TABLE students(
    student_id INT
);

Example 4- Temporary table does not belong to any schema, not even 'public'. The temporary table 'students' belongs to a schema called 'pg_temp_4', which is an alias for the current session's temporary schema unlike the permanent table which belongs to the default 'public' schema as shown in the previous example.

Drop Temporary Table

The command to drop a temporary table is same as that to drop a permanent table with no difference in syntax. Let us drop the table created in previous example. Here, we first execute the 'Drop table' command followed by the list all tables '\dt' command to find the temporary table ceases to exist and the permanent table getting listed back.

Drop table students;

Conclusion

In this article we explore the concept of PostgreSQL temporary table and its usage. As temporary tables could be very useful in some cases to keep temporary data, it is often a used in project research and analysis phase. We hope this article will you to get started on your PostgreSQL temporary tables journey.