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:
