PostgreSQL Temporary Tables: A Complete Guide

Learn how to create and use temporary tables in PostgreSQL. This tutorial explains their benefits, limitations, and how they differ from permanent tables. #PostgreSQL #TemporaryTables #SQL #Database #PostgreSQLTutorial

PostgreSQL Temporary Tables

Creating PostgreSQL Temporary Tables

A temporary table exists only for the duration of a database session. PostgreSQL automatically deletes them when the session ends. Use the CREATE TEMPORARY TABLE command to create one.

Syntax

The syntax for creating a temporary table is:

CREATE TEMPORARY TABLE temp_table_name(...);
            

You can also use TEMP instead of TEMPORARY:

CREATE TEMP TABLE temp_table_name(...);
            

Example: Creating and Using a Temporary Table

Let's create a temporary table named jti1:

CREATE TEMP TABLE jti1 (name VARCHAR);
            

(Assume you've already connected to a database using psql.)

This table is only accessible within the current session. If you create another session and try to access the jti1 table, it won't be found because it's automatically removed when the session ends.

Temporary Table Names

A temporary table can have the same name as a permanent table. However, while the temporary table exists, the permanent table will be inaccessible. Once the temporary table is dropped, the permanent table becomes accessible again.

Example: Creating a temporary table with the same name as a permanent table. The temporary table will be used instead of the permanent one until the temporary table is explicitly dropped.

CREATE TABLE fruits (fruits_name VARCHAR PRIMARY KEY, fruits_season VARCHAR NOT NULL);
CREATE TEMP TABLE fruits (fruits_name VARCHAR);
SELECT * FROM fruits; -- Accesses the temporary table
DROP TABLE fruits; -- Drops the temporary table; now the permanent table is accessible
            

Note: You usually don't need to specify the schema when creating a temporary table; PostgreSQL handles that automatically. Temporary tables are typically created in a special schema (e.g., pg_temp_).

Dropping PostgreSQL Temporary Tables

Use the DROP TABLE command to remove a temporary table. Unlike CREATE TABLE, DROP TABLE doesn't require TEMP or TEMPORARY for temporary tables.

Syntax for Dropping a Temporary Table

DROP TABLE temp_table_name;
            

Example: Dropping a Temporary Table

To drop the fruits temporary table from the example above:

DROP TABLE fruits;
            

Overview

We've learned how to create and drop temporary tables in PostgreSQL. Temporary tables are useful for short-term data storage within a session.