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.