CYBERTEC PostgreSQL Logo

Naming temporary tables properly in PostgreSQL

06.2015 / Category: / Tags:

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:

One table is a “real” one and the other one is temporary:

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:

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

… unless the schema is explicitly prefixed:

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.

2 responses to “Naming temporary tables properly in PostgreSQL”

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

  2. 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';

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram