A temporary table in PostgreSQL is a table that exists only for the duration of a single database session. After the session ends, the temporary table is automatically dropped. In this tutorial, you will learn how to create a temporary table in PostgreSQL.
Before proceeding with this tutorial, you should have:
- Access to a PostgreSQL server.
- Basic knowledge of SQL.
Step 1: Connect to the PostgreSQL Server
Connect to the PostgreSQL server using your preferred client. You can use the
psql command-line tool or a graphical client such as pgAdmin or DBeaver.
Step 2: Create a Temporary Table
To create a temporary table in PostgreSQL, you can use the
CREATE TEMPORARY TABLE statement followed by the table definition. For example, the following statement creates a temporary table named
temp_users with two columns:
CREATE TEMPORARY TABLE temp_users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL );
SERIAL data type is used for auto-incrementing columns in PostgreSQL. The
PRIMARY KEY constraint ensures that the
id column is unique and not null. The
NOT NULL constraint ensures that the
name column is not null.
Step 3: Insert Data into the Temporary Table
Once the temporary table is created, you can insert data into it using the
INSERT INTO statement. For example, the following statement inserts two rows into the
INSERT INTO temp_users (name) VALUES ('John Doe'), ('Jane Smith');
You can verify that the data was inserted successfully by running a
SELECT * FROM temp_users;
The output should be:
id | name ----+------------ 1 | John Doe 2 | Jane Smith
Step 4: Use the Temporary Table
You can use the temporary table just like any other table in PostgreSQL. For example, you can join it with other tables, filter rows based on certain criteria, or aggregate data. Here’s an example of a
SELECT statement that returns the count of distinct names in the
SELECT COUNT(DISTINCT name) FROM temp_users;
The output should be:
count ------- 2
Step 5: Drop the Temporary Table
When you’re done using the temporary table, you can drop it using the
DROP TABLE statement. For example, the following statement drops the
DROP TABLE temp_users;
The temporary table will be automatically dropped when the database session ends, but it’s a good practice to clean up after yourself.
- If you try to create a temporary table with the same name as an existing permanent table, you will get an error message:
ERROR: relation "my_table" already existsTo avoid this error, choose a unique name for your temporary table.
- If you try to use a temporary table outside of the database session that created it, you will get an error message:
ERROR: relation "temp_users" does not existThis is because temporary tables are automatically dropped at the end of the session. If you need to use the same temporary table in multiple sessions, you can create it again in each session.
In this tutorial, you learned how to create a temporary table in PostgreSQL, insert data into it, use it in queries, and drop it when you’re done. Temporary tables can be useful for storing data that’s only needed temporarily and don’t clutter up your database with unnecessary tables.