PostgreSQL - Temporary table name
Last Updated : 12 Jul, 2024
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.
Similar Reads
PostgreSQL - Temporary Table A PostgreSQL temporary table is a powerful tool for managing session-specific data that only needs to exist for a short duration. Temporary tables are created within a database session or transaction and are automatically dropped when the session ends, making them ideal for intermediate data storage
4 min read
PostgreSQL - Removing Temporary Table In PostgreSQL, one can drop a temporary table by the use of the DROP TABLE statement. Syntax: DROP TABLE temp_table_name; Unlike the CREATE TABLE statement, the DROP TABLE statement does not have the TEMP or TEMPORARY keyword created specifically for temporary tables. To demonstrate the process of d
2 min read
PostgreSQL - ALTER TABLE In PostgreSQL, the ALTER TABLE statement is a powerful and essential tool that allows us to modify the structure of an existing table to meet evolving database needs. With PostgreSQL ALTER TABLE, we can perform various modifications on the table without disrupting the ongoing operations of our datab
6 min read
PostgreSQL - ALTER TABLESPACE In PostgreSQL, a tablespace is a storage location where database objects like tables and indexes are stored. Managing tablespaces effectively is crucial for optimizing performance and resource allocation in your database environment. PostgreSQL provides the ALTER TABLESPACE statement, allowing you t
2 min read
PostgreSQL - CREATE TABLESPACE In PostgreSQL, a tablespace is a mechanism used to map a logical name to a physical location on disk. Simply put, a tablespace is a disk location where PostgreSQL stores all its database objects, such as tables, indexes, and more. Understanding and utilizing tablespaces effectively can significantly
2 min read