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 ...
Table of Contents
Let us create two tables which have the same name:
|
1 2 |
test=# CREATE TABLE x (id int); CREATE TABLE |
One table is a “real” one and the other one is temporary:
|
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:
|
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.
When “x” is selected, the temporary table comes first ...
|
1 2 3 4 5 |
test=# SELECT * FROM x; a | b ---+--- (0 rows) |
… unless the schema is explicitly prefixed:
|
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 Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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.
Better yet, don't put tables in public and always use fully qualified (including schema) names. Problem solved. ;P
By the way, you can safely refer to any temporary object using the pg_temp schema. This even allows you to do things like create temporary functions:
CREATE FUNCTION pg_temp.test() RETURNS int LANGUAGE sql IMMUTABLE AS 'SELECT 1';