Viswamitra's blog

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,
.....
.....) ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
[ 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;
 CREATE DATABASE
 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);
 CREATE TABLE
 test_temp=# insert into emp values (1,'Viswamitra');
 INSERT 0 1
 test_temp=# create temporary table emp (id int, name varchar);
 CREATE TABLE 
 test_temp=# \dt
           List of relations
   Schema   | Name | Type  |  Owner   
 -----------+------+-------+----------
  pg_temp_4 | emp  | table | postgres
 (1 row)
 test_temp=# show search_path ;
    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);
 CREATE TABLE
 test_temp=# select pg_relation_filepath('test_size');
  pg_relation_filepath
 base/90113/t4_90140
 (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=#
 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';
 CREATE TABLESPACE
 test_temp=# create temporary table test_size_1(id int, name varchar) tablespace test;
 CREATE TABLE
 test_temp=# select pg_relation_filepath('test_size_1');
               pg_relation_filepath
 pg_tblspc/90147/PG_10_201707211/90113/t4_90148
 (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.