How to create a postgresql temporary table?

Creating a temporary table in PostgreSQL is straightforward and can be very useful for short-term data manipulation within a session. Temporary tables are session-specific and are automatically dropped at the end of the session or transaction in which they are created.

Creating a Temporary Table

You can create a temporary table using the CREATE TEMPORARY TABLE statement. Here's a step-by-step guide on how to create and use a temporary table in PostgreSQL:

Syntax

  CREATE TEMPORARY TABLE temp_table_name (    column1 datatype [constraints],    column2 datatype [constraints],    ...);

Example

Let's create a temporary table to store some user information.

  CREATE TEMPORARY TABLE temp_users (    user_id SERIAL PRIMARY KEY,    username VARCHAR(50) NOT NULL,    email VARCHAR(100) NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Inserting Data into a Temporary Table

You can insert data into the temporary table just like any other table.

  INSERT INTO temp_users (username, email)VALUES ('john_doe', 'john.doe@example.com'),       ('jane_smith', 'jane.smith@example.com');

Querying Data from a Temporary Table

You can query data from the temporary table as you would from any other table.

  SELECT * FROM temp_users;

Dropping a Temporary Table

Although temporary tables are automatically dropped at the end of the session, you can explicitly drop them if needed.

  DROP TABLE temp_users;

Key Points to Remember

Scope: Temporary tables are only visible within the session that created them. Different sessions can have temporary tables with the same name without conflict.

Automatic Dropping: Temporary tables are automatically dropped at the end of the session or transaction.

Performance: Temporary tables are often stored in memory, making them faster for certain operations, but this can depend on the size of the data and the PostgreSQL configuration.

Advanced Usage

Temporary Tables with ON COMMIT Clause

You can specify what should happen to the temporary table at the end of a transaction using the ON COMMIT clause.

  CREATE TEMPORARY TABLE temp_users (    user_id SERIAL PRIMARY KEY,    username VARCHAR(50) NOT NULL,    email VARCHAR(100) NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ON COMMIT DELETE ROWS;