August 2, 2023
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 name 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:
CREATE TEMPORARY TABLE temp_table_name( column_list );
In this syntax:
- First, specify the name of the temporary table after the
CREATE TEMPORARY TABLEkeywords.
- Second, specify the column list, which is the same as the one in the
TEMPORARY keywords are equivalent so you can use them interchangeably:
CREATE TEMP TABLE temp_table( ... );
A temporary table is visible only to the session that creates it. In other words, it is invisible to other sessions.
Let’s take a look at an example.
First, log in to the PostgreSQL database server using the
psql program and create a new database named
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:
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
test=# SELECT * FROM mytemp; ERROR: relation "mytemp" does not exist LINE 1: SELECT * FROM mytemp;
As can see clearly from the output, the second session could not see the
mytemp table. Only the first session can access it.
After that, quit all the sessions:
Finally, log in to the database server again and query data from the
test=# SELECT * FROM mytemp; ERROR: relation "mytemp" does not exist LINE 1: SELECT * FROM mytemp; ^
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 have the same name as a permanent table, even though it is not recommended.
When you create a temporary table that shares 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
CREATE TABLE customers( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL );
Second, create a temporary table with the same name as the
CREATE TEMP TABLE customers( customer_id INT );
Now, query data from the
SELECT * FROM customers; customer_id ------------- (0 rows)
This time PostgreSQL accessed the temporary table
customers instead of the permanent one.
Note that PostgreSQL creates temporary tables in a special schema, therefore, you cannot 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:
List of relations Schema | Name | Type | Owner -----------+------------------+----------+---------- pg_temp_3 | customers | table | postgres public | customers_id_seq | sequence | postgres (2 rows)
The output shows the schema of the
customers temporary table is
In this case, to access the permanent table, you need to use a fully qualified name i.e., prefixed with the schema. For example:
SELECT * FROM public.customers;
Removing a PostgreSQL temporary table
To drop a temporary table, you use the
DROP TABLE statement. The following statement uses the DROP TABLE statement to drop a temporary table:
DROP TABLE temp_table_name;
CREATE TABLE statement, the
DROP TABLE statement does not have the
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:
DROP TABLE customers;
If you list the tables in the
test database again, the permanent table
customers will appear as follows:
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.