Summary: in this tutorial, you will learn about the PostgreSQL temporary table and how to manage it effectively.
Creating a PostgreSQL temporary table
A temporary table, as its named implied, 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.
To create a temporary table, you use the CREATE TEMPORARY TABLE statement.
1 2 3 | CREATE TEMPORARY TABLE temp_table( ... ); |
It is possible to use TEMP instead of TEMPORARY keyword in the statement above:
1 2 3 | 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.
Let’s take a look at an example.
First, login to the PostgreSQL database server using the psql program and create a new database named test:
1 2 3 4 | postgres=# CREATE DATABASE test; CREATE DATABASE postgres-# \c test; You are now connected to database "test" as user "postgres". |
Next, create a temporary table named mytemp as follows:
1 2 3 4 5 6 | test=# CREATE TEMP TABLE mytemp(c INT); CREATE TABLE test=# SELECT * FROM mytemp; c --- (0 rows) |
Then, launch another session that connects to the test database and query data from the mytemp table:
1 2 3 | test=# SELECT * FROM mytemp; ERROR: relation "mytemp" does not exist LINE 1: SELECT * FROM mytemp; |
As can see, the second session could not see the mytemp table. Only the first session can access it.
After that, quit all the sessions:
1 | test=# \q |
Finally, login to the database server again and query data from the mytemp table:
1 2 3 4 | test=# SELECT * FROM mytemp; ERROR: relation "mytemp" does not exist LINE 1: SELECT * FROM mytemp; ^ |
The mytemp table does not exist because it has been dropped automatically when the session ended, therefore, PostgreSQL issued an error.
PostgreSQL temporary table name
A temporary table can share the same name with a permanent table, even though it is not recommended. When you create a temporary table with the same name as a permanent table, you cannot access the permanent table until the temporary table is removed. Consider the following example:
First, create a table named customers:
1 | test=# CREATE TABLE customers(id SERIAL PRIMARY KEY, name VARCHAR NOT NULL); |
Second, create a temporary table with the same name: customers
1 | test=# CREATE TEMP TABLE customers(customer_id INT); |
Now, we query data from the customers table:
1 2 3 4 | test=# SELECT * FROM customers; customer_id ------------- (0 rows) |
This time PostgreSQL accessed the temporary table customers instead of the permanent one. From now on, you can only access the permanent customers table in the current session when the temporary table customers is removed explicitly.
Note that PostgreSQL creates temporary tables in a special schema, therefore, you must not specify the schema in the CREATE TEMP TABLE statement.
If you list the tables in the test database, you will only see the temporary table customers, not the permanent one:
1 2 3 4 5 6 | List of relations Schema | Name | Type | Owner -----------+------------------+----------+---------- pg_temp_3 | customers | table | postgres public | customers_id_seq | sequence | postgres (2 rows) |
The output shows that the schema of the customers temporary table is pg_temp_3.
Removing a PostgreSQL temporary table
To drop a temporary table, you use the DROP TABLE statement. The following statement illustrates how to drop a temporary table:
1 | DROP TABLE temp_table_name; |
Unlike the CREATE TABLE statement, the DROP TABLE statement does not have the TEMP or TEMPORARY keyword created specifically for temporary tables.
For example, the following statement drops the temporary table customers that we have created in the above example:
1 | test=# DROP TABLE customers; |
If you list the tables in the test database again, the permanent table customers will appear as follows:
1 2 3 4 5 6 7 | test=# \d List of relations Schema | Name | Type | Owner --------+------------------+----------+---------- public | customers | table | postgres public | customers_id_seq | sequence | postgres (2 rows) |
In this tutorial, you have learned about the temporary table and how to create and drop it using CREATE TEMP TABLE and DROP TABLE statements.