Naming temporary tables properly in PostgreSQL

06.2015 / Category: How To / Tags: table

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 which have the same name:

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

1 2	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

1 2	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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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)

1

2

3

4

5

test=# SELECT * FROM x;

a | b

---+---

(0 rows)

… unless the schema is explicitly prefixed:

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

(0 rows)

1

2

3

4

5

test=# SELECT * FROM public.x;

id

----

(0 rows)

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

For further advice on managing tables in PostgreSQL, see our latest blogs in the table tag blog spot.

In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes

Article Rating

Subscribe

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Pete Hollobon

8 years ago

That's because if "pg_temp" is not in search_path, it's implicitly searched first. If you explicitly put it last (something like set search_path to "$user",public,pg_temp;), permanent tables will have precedence over temporary ones.

0

Reply