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.