PostgreSQL – Temporary Table

A temporary table, as the name implies, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction.

Syntax:
CREATE TEMPORARY TABLE temp_table(
   ...
);

or,

CREATE TEMP TABLE temp_table(
   ...
);

A temporary table is visible only to the session that creates it. In other words, it remains invisible to other sessions.

Example:

First, we create a new database test as follows:

CREATE DATABASE test;

Next, create a temporary table named mytemp as follows:

CREATE TEMP TABLE mytemp(c INT);

Then, launch another session that connects to the test database and query data from the mytemp table:

SELECT * FROM mytemp;

It will raise an error as the second session cannot see mytemp table. So, quit all sessions after that as follows:

\q

Finally, login to the database server again and query data from the mytemp table:

SELECT * FROM mytemp;

The mytemp table does not exist because it has been dropped automatically when the session ended, therefore, PostgreSQL issued an error.

Output: