Using temporary tables the way they should not be used

Temporary tables are a core feature of SQL and are commonly used by people around the globe. PostgreSQL provides a nice implementation of temporary tables, which has served me well over the years. An interesting question arises when using a temporary table: What if a temporary table has the same name as a “real” table? What happens? The answer might be quite surprising …

Creating two tables …

Let us create two tables having the same name:

test=# CREATE TABLE x (id int);
CREATE TABLE

One table is a “real” one and the other one is temporary:

test=# CREATE TEMP TABLE x (a int, b int);
CREATE TABLE

The first vital observation is that both tables can actually be created without any problems. PostgreSQL will put those tables into two totally separate namespaces:

test=# \dt
        List of relations
  Schema   | Name | Type  | Owner
-----------+------+-------+-------
 pg_temp_2 | x    | table | hs
(1 row)


test=# \dt public.
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | x    | table | hs

(1 row)

The crucial point is that the temporary table has precedence over the persistent table.

What PostgreSQL does …

When “x” is selected, the temporary table comes first …

test=# SELECT * FROM x;
 a | b
---+---

(0 rows)

… unless the schema is explicitly prefixed:

test=# SELECT * FROM public.x;
 id
----

(0 rows)

The recommendation is absolutely clear here: Don’t use temporary tables having the same name as persistent tables. All it does is causing trouble and pain.