PostgreSQL -Temporary Tables

Temporary tables are tables that are created and used for the duration of a single database session. They are typically used to store temporary or intermediate data that you need to work with within a specific session.

Temporary tables are automatically dropped at the end of the session or when the connection is closed, so they don’t persist beyond the context in which they were created.

Creating Temporary Tables:

To create a temporary table, use the CREATE TEMPORARY TABLE command:

CREATE TEMPORARY TABLE temp_table ( id serial PRIMARY KEY, name text ); 

Temporary tables are created in the same way as regular tables, but with the TEMPORARY keyword.

Inserting Data into Temporary Tables:

You can insert data into a temporary table just like you would with a regular table:

INSERT INTO temp_table (name) VALUES ('John'), ('Jane');

Querying Temporary Tables:

Temporary tables can be queried and manipulated just like regular tables:

SELECT * FROM temp_table;

Dropping Temporary Tables:

Temporary tables are automatically dropped at the end of the session or when the connection is closed. However, if you want to explicitly drop a temporary table before the end of the session, you can use the DROP TABLE command:

DROP TABLE temp_table;

Local vs Global Temporary Tables:

PostgreSQL supports both local and global temporary tables. Local temporary tables are private to the session that created them and are dropped at the end of the session. Global temporary tables are visible to all sessions but are still dropped at the end of the session that created them. To create a global temporary table, use the CREATE TEMPORARY TABLE command with the ON COMMIT DELETE ROWS option:

CREATE TEMPORARY TABLE global_temp_table ( id serial PRIMARY KEY, name text ) 
ON COMMIT DELETE ROWS;

Temporary tables can be useful when you need to perform complex calculations, intermediate result storage, or data manipulation within a single session without cluttering the database with long-term table structures. The data in temporary tables is session-specific and doesn’t persist across different sessions or connections.