Temporary tables in PostgreSQL

Temporary tables are short lived tables which will be dropped at the end of session or transaction. These are used to store intermediate data in a transaction. Temporary tables can be created in the same way as the permanent tables where we just add TEMP or TEMPORARY to create table statement. Below is the syntax to create temporary tables

create [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } table
col1 datatype,
col2 datatype,
[ TABLESPACE tablespace_name ]

Databases like Oracle or SQL server support global temporary tables where a temporary table can be created once and used in all sessions, but in PostgreSQL it is not yet supported, the GLOBAL keyword is there just for SQL standard but it actually represents the LOCAL.

Temporary tables are visible only in a session, sessions can’t see temporary tables of each other. Temporary tables can be created with same name as the permanent tables but it is not recommended. When you try to access the table without schema, temporary tables will be given priority than permanent tables.

In the below example temporary table with name emp has more priority than permanent emp table. To access the permanent tables use fully qualified name (schema.table).

 postgres=# create database test_temp;
 postgres=# \c test_temp
 psql (12.4, server 10.14)
 You are now connected to database "test_temp" as user "postgres".
 test_temp=# create table emp(id int, name varchar);
 test_temp=# insert into emp values (1,'Viswamitra');
 test_temp=# create temporary table emp (id int, name varchar);
 test_temp=# \dt
           List of relations
   Schema   | Name | Type  |  Owner   
  pg_temp_4 | emp  | table | postgres
 (1 row)
 test_temp=# show search_path ;
 "$user", public
 (1 row)
 test_temp=# \dt public.*
         List of relations
  Schema | Name | Type  |  Owner   
  public | emp  | table | postgres
 (1 row)
 test_temp=# select * from emp;
  id | name 
 (0 rows)
 test_temp=# select * from public.emp;
  id |    name    
   1 | Viswamitra
 (1 row)

Temporary tables will be created in a special schema with naming convention “pg_temp_nnn”, temporary tables can not be created in non-temporary schemas. When a query is executed , PostgreSQL will search for matching tables in pg_temp_nnn schema and then it will search in schemas specified in search_path variable.

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

By default temporary tables(files store the data) will be created in the default tablespace pg_default, if there is any tablespace created and set in the parameter temp_tablespaces, tables will use that tablespace.

 test_temp=# create temporary table test_size(id int, name varchar);
 test_temp=# select pg_relation_filepath('test_size');
 (1 row)
 test_temp=# ! du -sh /pgdata/10/data/base/90113/t4_90140
 0       /pgdata/10/data/base/90113/t4_90140
 test_temp=# insert into test_size select generate_series(1,10000),md5(generate_series(1,10000)::text);
 INSERT 0 10000
 test_temp=# ! du -sh /pgdata/10/data/base/90113/t4_90140
 960K    /pgdata/10/data/base/90113/t4_90140
 test_temp=# \dt+ test_size
                         List of relations
   Schema   |   Name    | Type  |  Owner   |  Size  | Description
  pg_temp_4 | test_size | table | postgres | 704 kB |
 (1 row)
 test_temp=# ! mkdir /tmp/test
 test_temp=# create tablespace test location '/tmp/test';
 test_temp=# create temporary table test_size_1(id int, name varchar) tablespace test;
 test_temp=# select pg_relation_filepath('test_size_1');
 (1 row)

Indexes can be created on temporary tables which are by default temporary and dropped after that. autovacuum process can not handle the temporary tables so it is highly recommended to analyze the temporary tables before using in complex queries.

temp_buffers is a session level memory component which is allocated each session to access the temporary tables, by default the size of temp_buffers is 8 MB. if the temporary tables are too large than temp_buffers query will start using disk (merge disk algorithm) to process the data.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s