dark

How to Create a Temporary Table in PostgreSQL

How to Create a Temporary Table in PostgreSQL

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.

Prerequisites

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: id and name.

CREATE TEMPORARY TABLE temp_users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

The 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 temp_users table:

INSERT INTO temp_users (name)
VALUES ('John Doe'), ('Jane Smith');

You can verify that the data was inserted successfully by running a SELECT statement:

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 temp_users table:

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 temp_users table:

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.

Troubleshooting

  • 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 exists To 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 exist This 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.

Conclusion

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.

Related tutorials