PostgreSQL Temporary Table: CREATE TEMP TABLE Explained

In PostgreSQL, a temporary table is a session-scoped table that exists only for the duration of the current database session. When the connection closes, PostgreSQL automatically drops the temporary table and frees any associated resources.

Temporary tables are particularly useful for intermediate calculations, complex multi-step data transformations, and staging rows before batch updates without cluttering permanent database schemas.

Creating a PostgreSQL Temporary Table

You create a temporary table using the CREATE TEMPORARY TABLE or CREATE TEMP TABLE statement:

CREATE { TEMPORARY | TEMP } TABLE temp_table_name (
   column_name data_type [column_constraint]
   [, ...]
   [table_constraint]
);

Compared with a regular CREATE TABLE statement, adding the TEMPORARY (or TEMP) keyword indicates that PostgreSQL should allocate the table in a session-specific schema (such as pg_temp_N).

[!NOTE] If you create a temporary table with the same name as an existing permanent table in the same schema, the temporary table takes precedence for the current session. The permanent table remains untouched and becomes visible again once the temporary table is dropped or your session terminates.

Dropping a Temporary Table

PostgreSQL automatically drops temporary tables at session disconnect. However, if you want to free resources earlier, you can explicitly drop it using DROP TABLE:

DROP TABLE temp_table_name;

PostgreSQL Temporary Table Examples

Creating a Basic Temporary Table

The following statement creates a temporary table named test_temp:

CREATE TEMP TABLE test_temp (
  id SERIAL PRIMARY KEY,
  notes VARCHAR
);

Use the \dt meta-command in psql to list relations in the current search path:

\dt

               List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | postgres
 public    | users             | table | postgres
(2 rows)

Notice that the temporary table belongs to the pg_temp_4 schema, while permanent tables reside in public.

Shadowing a Permanent Table with a Temporary Table

Suppose we have an existing permanent table named users in the public schema:

\d users

                          Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           | not null |
 name       | character varying(45)       |           | not null |
 age        | integer                     |           |          |
 locked     | boolean                     |           | not null | false
 created_at | timestamp without time zone |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Now, create a temporary table with the exact same name users:

CREATE TEMP TABLE users (
  id SERIAL PRIMARY KEY
);

Run \dt to inspect how PostgreSQL resolves relations:

\dt

               List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | postgres
 pg_temp_4 | users             | table | postgres
(2 rows)

PostgreSQL automatically places pg_temp_* at the front of search_path. As a result, unqualified references to users resolve to pg_temp_4.users. To query the original permanent table while the temporary table exists, prefix the table name with its schema: SELECT * FROM public.users.

Dropping the Temporary Table

When you drop the temporary table:

DROP TABLE users;

And re-list relations:

\dt

               List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | postgres
 public    | users             | table | postgres
(2 rows)

The temporary table is removed, and the permanent public.users table becomes the default target for unqualified queries once again.

Conclusion

PostgreSQL temporary tables provide a session-local workspace for staging and aggregating intermediate results. Each session accesses its own temporary table data, which keeps that temporary data separate between sessions.

For related table techniques, see creating tables, dropping tables, and creating tables from queries with SELECT INTO.