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
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 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.