It has been quite a while since the last posts in this series, so here is what we looked at until now:

In this post we’ll look at temporary tables. Temporary tables hold data for the scope of a session or a transaction and the data is lost afterwards, so what are they good for then? Usually you will use them to store intermediate results that you need for further processing without the need to permanently store these. A typical use case is a business report that requires some intermediate aggregations. Can we do this in PostgreSQL? Yes, of course. Lets go.

When we look at the create table syntax the keywords TEMP or TEMPORARY are there:

(postgres@[local]:5439) [postgres] > h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

Then we should be able to create a temporay table, shouldn’t we?

(postgres@[local]:5439) [postgres] > create temporary table tmp1 ( a int, b varchar(10));
CREATE TABLE
(postgres@[local]:5439) [postgres] > d tmp1
           Table "pg_temp_2.tmp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

Easy. Have you looked at the schema it got created in? What is pg_temp_2? I do not have schema that is named pg_temp_2:

(postgres@[local]:5439) [postgres] > dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

Temporay tables get created in a special schema. You can see it if you query pg_namespace directly:

(postgres@[local]:5439) [postgres] > select nspname from pg_namespace where nspname = 'pg_temp_2';
  nspname  
-----------
 pg_temp_2
(1 row)

Important to know: When you exit from your session the temporary table is gone:

(postgres@[local]:5439) [postgres] > d tmp1
           Table "pg_temp_2.tmp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

(postgres@[local]:5439) [postgres] > q
postgres@pgbox:/home/postgres/ [PG961] psql postgres
psql (9.6.1 dbi services build)
Type "help" for help.

(postgres@[local]:5439) [postgres] > d tmp1
Did not find any relation named "tmp1".

What about the visibiliy of the data in a temporary table? It depends on how you create the table. When you want the data
to be visible for the entire lifetime of the session you do it like above:

(postgres@[local]:5439) [postgres] > create temporary table tmp1 ( a int, b varchar(10));
CREATE TABLE
(postgres@[local]:5439) [postgres] > insert into tmp1 values (1,'1');
INSERT 0 1
(postgres@[local]:5439) [postgres] > select * from tmp1;
 a | b 
---+---
 1 | 1
(1 row)

When you want the data to be visible only for the duration of the current transaction you do it like this:

(postgres@[local]:5439) [postgres] > create temporary table tmp2 ( a int, b varchar(10)) on commit delete rows;
CREATE TABLE
(postgres@[local]:5439) [postgres] > begin;
BEGIN
(postgres@[local]:5439) [postgres] > insert into tmp2 values (1,'1');
INSERT 0 1
(postgres@[local]:5439) [postgres] > end;
COMMIT
(postgres@[local]:5439) [postgres] > select count(*) from tmp2;
 count 
-------
     0
(1 row)

Another important point to know: When you create a temporary table with the same name as an existing table you will not see
the existing table anymore unless you prefix the normal table with the schema name:

(postgres@[local]:5439) [postgres] > create table tmp3 ( a int );
CREATE TABLE
(postgres@[local]:5439) [postgres] > create temporary table tmp3 ( a int, b varchar(10));
CREATE TABLE
(postgres@[local]:5439) [postgres] > d tmp3
           Table "pg_temp_2.tmp3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

(postgres@[local]:5439) [postgres] > d public.tmp3
     Table "public.tmp3"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

Be careful with this. What about performance? Is a temporary table faster to insert than a normal table?

(postgres@[local]:5439) [postgres] > create table tmp1 ( a int, b varchar(10) );
CREATE TABLE
(postgres@[local]:5439) [postgres] > create temporary table tmp2 ( a int, b varchar(10) );
CREATE TABLE

This is the script:

(postgres@[local]:5439) [postgres] > ! cat a.sql
truncate tmp1;
truncate tmp2;
with aa as
( select generate_series ( 1, 1000000 ) a  )
insert into tmp1 (a,b)
       select a, a
         from aa;

with aa as
( select generate_series ( 1, 1000000 ) a  )
insert into tmp2 (a,b)
       select a, a
         from aa;

This is the result:

(postgres@[local]:5439) [postgres] > i a.sql
TRUNCATE TABLE
Time: 19.626 ms
TRUNCATE TABLE
Time: 35.665 ms
INSERT 0 1000000
Time: 7777.034 ms
INSERT 0 1000000
Time: 3327.984 ms
(postgres@[local]:5439) [postgres] > i a.sql
TRUNCATE TABLE
Time: 46.221 ms
TRUNCATE TABLE
Time: 33.286 ms
INSERT 0 1000000
Time: 7425.957 ms
INSERT 0 1000000
Time: 3241.140 ms
(postgres@[local]:5439) [postgres] > i a.sql
TRUNCATE TABLE
Time: 44.365 ms
TRUNCATE TABLE
Time: 35.992 ms
INSERT 0 1000000
Time: 8732.566 ms
INSERT 0 1000000
Time: 3888.795 ms

The temporary table is almost double as fast to write to than the normal table. Of course you can create indexes on temporary tables as well:

(postgres@[local]:5439) [postgres] > create temporary table tmp4 ( a int, b varchar );
CREATE TABLE
(postgres@[local]:5439) [postgres] > create index tmpi1 on tmp4(a);
CREATE INDEX

.. and of course the index is gone as well once you end your session.

Another point to remember: Temporary tables are not visible to the vacuum deamon. You might think this is not a problem as they disappear anyway but remember that autovacuum is responsible for gathering the statistics (kicking off analyze) as well.
Depending on what you want to do with the data you loaded into the temporary table it might be wise to issue a manual analyze on it:

(postgres@[local]:5439) [postgres] > analyze verbose tmp4;

I hope I do not need to say that tempoary tables are not crash safe 🙂 Have fun …