In PostgreSQL, it is possible to define a temp/temporary table with the exact duplicate name as a permanent/regular table. However, it is not recommended because of possible confusion/ambiguity. For instance, it would not be an easy task to differentiate between temporary and permanent tables when working with complex databases. Moreover, when a temporary table is created with the same name as a regular table, then the user's access to the permanent table will be temporarily denied until the temporary table is dropped.

Can a Temp Table Have the Exact Same Name as a Regular/Permanent Postgres Table?

Yes! A permanent and a temp table can have the same name at the same time. However, database experts recommend the use of distinct names for temporary and permanent tables to avoid ambiguity in queries and database operations.

Let’s comprehend this concept practically!

Example: Creating Temp and Regular Tables With the Same Name

First, create a regular PostgreSQL table named “cp_table” by executing the following query:

CREATE TABLE cp_table(
id INT,
name TEXT,
email VARCHAR);

Now verify the table’s structure by executing the below-provided “SELECT” command:

SELECT * FROM cp_table;

The above snippet confirms the existence of the “cp_table”. Now create a TEMP table with the exact duplicate name:

CREATE TEMP TABLE cp_table(
cp_id INT,
cp_name TEXT
);

Utilize the following command with the table’s name to confirm its creation:

SELECT * FROM cp_table;

The below snippet verifies that a temp table is created with the same name as a permanent table already has:

Now, in the current session, whenever you fetch the “cp_table”, Postgres will retrieve the result set for the temporary table. To fetch the data of the permanent table, either you have to wait for the current session to expire or drop the temporary table explicitly.

Conclusion

In PostgreSQL, it is possible to create a temp table with the exact duplicate name as a permanent/regular table. However, database experts recommend the use of distinct names for temporary and permanent tables to avoid ambiguity in queries and database operations. This post has illustrated a practical guide on can a temp table have the exact same name as a permanent/regular table in PostgreSQL.