PostgreSQL - Temporary table name

In PostgreSQL, it is possible, though not recommended, to create a temporary table with the same name as an existing permanent table. When a temporary table shares its name with a permanent table, the temporary table will take precedence, preventing access to the permanent table until the temporary table is removed. This behavior can lead to confusion and should be managed carefully.

Let us get a better understanding of the Temporary Table Name in PostgreSQL from this article.

PostgreSQL Temporary table name Example

Let us take a look at an example of the Temporary Table Name in PostgreSQL to better understand the concept.

Step 1: Create a Permanent Table

First, create a permanent table named 'customers'.

Query:

CREATE TABLE customers(
                           id SERIAL PRIMARY KEY, 
                           name VARCHAR NOT NULL
);

This table is now ready to store customer data with unique IDs and names.

Step 2: Create a Temporary Table with the Same Name

Next, create a temporary table named 'customers'.

Query:

CREATE TEMP TABLE customers(
                              customer_id INT
);

Step 3: Querying the customers Table

Now query the data from the customers table as below.

Query:

SELECT * FROM customers;

Output: PostgreSQL will access the temporary customers table instead of the permanent one. This is because the temporary table takes precedence in the current session.

Step 4: Listing Tables in the Database

If you list the tables in the test database using the \dt command.

\dt

The result is as shown below:

Output: The output will show the temporary 'customers' table, but not the permanent one. The schema for the temporary table will be something like 'pg_temp_3'.

Important Points About PostgreSQL Temporary table name

• PostgreSQL creates temporary tables in a special schema, so you should not specify the schema in the 'CREATE TEMP TABLE' statement.
• To access the permanent customers table again, you must remove the temporary table using the DROP TABLE statement.
• When a temporary table has the same name as a permanent table, the temporary table takes precedence in the current session.
• When listing tables using the '\dt' command, PostgreSQL will display the temporary table, not the permanent one, as long as the temporary table exists.