Create a Temp Table in PostgreSQL

Introduction

When you’re working with large tables in PostgreSQL, you might find that you need to interact with a certain subset of rows in a table over and over again. Instead of repeatedly filtering your table to get that subset of data, it’s more efficient to fetch those rows once and store them in a temporary table. This tutorial will teach you how to create a temp table and later remove it when you’re done.

Prerequisites

If you’d like to follow along with the PostgreSQL examples in this tutorial, make sure that you have PostgreSQL server installed and configured on your machine. The service needs to be running in the background.

Linux and Windows users can download PostgreSQL here.

What is a PostgreSQL Temporary Table?

Temporary tables exist in their own unique schema, so you can’t assign a schema name when you create this kind of table. A temporary table only exists as long as its database session– this means that PostgreSQL automatically drops the temporary table at the end or a transaction or a session.

Sample Use Case for a PostgreSQL Temporary Table

Temporary tables are useful for application developers and database administrators that interact with large databases on a regular basis. They’re especially valuable in situations that require the repeated querying or processing of a small subset of data within a larger table.

In this cases, we can store the filtered data in the temporary table; we can then use this temporary table over and over without going to the actual database. These tables reside inside tempdb, which is also a database system.

There are two ways to create a temporary table. First, we can use the TEMPORARY clause in the CREATE TABLE statement:

Alternatively, we can use the TEMP clause:

Temporary tables are only visible within the session in which it was created; no other sessions will be able to view it.

Creating a Temporary Table Example

To create a temporary table, we first need to connect to our PostgreSQL server using the psql command. Then, we’ll create a database, naming it dbsandbox.

We can accomplish this using the following statement:

Once the database is created, it’s time to create our temporary table using the following statement:

With this statement, we created our TEMP table named temptbl within the sandboxdb database.

To verify that the table was successfully created, we can use a SELECT statement:

The result of our SELECT should look like the following:

Earlier in this section, we mentioned that a temporary table is only visible to the current session in which it was created. Let’s test and see if that’s true by creating another session in PostgreSQL. In this new session, we’ll try to connect to the temptable. To do this, first quit the current connection using the command \q, then try to reconnect again using the previous steps. An example of this process is shown below:

Note that we got an error when we tried to execute the SELECT operation against the temporary table temptbl. This occurs because the temptbl was immediately dropped by PostgreSQL as soon as we executed the command \q and ended that particular session.

Removing A Temporary Table Example

In the previous section, we learned how to create a temporary table. Now, let’s talk about how to remove or drop a temporary table.

Shown below is the syntax used to drop a temporary table:

Notice that the DROP TABLE statement doesn’t have clauses like TEMPORARY and TEMP, unlike the CREATE TABLE statement.

Let’s say we wanted to drop the temptbl during its session– we can do so using the following statement:

Conclusion

There’s no doubt that temporary tables can help you optimize your database interactions, allowing you to store a smaller subset of rows from a larger table instead of filtering the same data again and again. In this tutorial, we explained how to create a temp table and how to drop one using the CREATE TEMP TABLE and DROP TABLE statements. Using the examples we provided as a guide, you’ll be able to create and manage temporary tables for your own database interactions.