In which schema do temporary tables go in PostgreSQL?

As you might know, you can create temporary tables, views and sequences in PostgreSQL. Temporary means, that the temporary table, view or sequence will be dropped when you either end your session or you transaction. When the drop will happen depends on the options you used when you created the object. As all objects in PostgreSQL are stored in a schema, temporary objects need to be created in a schema as well. But when you try to explicitly specify a schema for a temporary object, you’ll get an error message. Let’s have a look.

To confirm that we are not allowed to specify a schema for a temporary object, we try to do so:

postgres=# create temporary table public.tt ( a int );
ERROR:  cannot create temporary relation in non-temporary schema
LINE 1: create temporary table public.tt ( a int );
                               ^

The error message clearly states that these types of objects need to go into a temporary schema, but a temporary schema cannot be created:

postgres=# \h create schema
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER
URL: https://www.postgresql.org/docs/devel/sql-createschema.html

This somehow needs to happen automatically. Once more, let’s create a temporary table, but this time we do not specify a schema:

postgres=# create temporary table tt ( a int );
CREATE TABLE

Where did it go to? If we ask for the list of schemas there is only public, and we already know from the error message above that it cannot be public:

postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)

The answer becomes clear once we list the system schemas as well:

postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_temp_3          | postgres
 pg_toast           | postgres
 pg_toast_temp_3    | postgres
 public             | pg_database_owner
(6 rows)

We got two new schema after we created the temporary table: One for the temporary table itself and another one for toasted data, if any. To verify that the temporary table really is in the schema we can either full reference the table in a select:

postgres=# select * from pg_temp_3.tt;
 a 
---
(0 rows)

… or we can always ask pg_class:

postgres=# select relnamespace::regnamespace from pg_class where relname = 'tt';
 relnamespace 
--------------
 pg_temp_3
(1 row)

This temporary schema will not be dropped once we end our session and will still be there if we reconnect. The temporary table, however, was dropped:

postgres=# \q
14:52:39 postgres@debian11pg:/home/postgres/ [pgdev] psql 
psql (16devel)
Type "help" for help.

postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_temp_3          | postgres
 pg_toast           | postgres
 pg_toast_temp_3    | postgres
 public             | pg_database_owner
(6 rows)

postgres=# select * from pg_temp_3.tt;
ERROR:  relation "pg_temp_3.tt" does not exist
LINE 1: select * from pg_temp_3.tt;
                      ^

The temporary schema will be re-used for future temporary objects:

postgres=# create temporary table xx ( a int );
CREATE TABLE
postgres=# select relnamespace::regnamespace from pg_class where relname = 'xx';
 relnamespace 
--------------
 pg_temp_3
(1 row)

One more thing to know: If someone, in another session, also creates a temporary object there will be a new temporary schema created:

postgres@debian11pg:/home/postgres/ [pgdev] psql
psql (16devel)
Type "help" for help.

postgres=# create temporary table hh ( a int );
CREATE TABLE
postgres=# \dnS
            List of schemas
        Name        |       Owner       
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_temp_3          | postgres
 pg_temp_4          | postgres
 pg_toast           | postgres
 pg_toast_temp_3    | postgres
 pg_toast_temp_4    | postgres
 public             | pg_database_owner
(8 rows)

postgres=# 

This means, that you potentially can have many temporary schemas.