How to create a postgresql temporary table?

981 Asked by EdythFerrill in SQL Server , Asked on Oct 3, 2022

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes.

I looked at the documentation for CREATE TABLE and it says VALUES can be used as a query but gives no example; the documentation for the VALUES clause linked therein does not have an example either?

So, I wrote a simple test as follows:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup (
  key integer,
  val numeric
) AS
VALUES (0,-99999), (1,100);
But PostgreSQL (9.3) is complaining about
syntax error at or near "AS"

My questions are:

How can I fix the statement above?

How can I adapt it to be used in a WITH block?

Answered by Elayne Balding

create postgresql temporary table as needs a select statement:

DROP TABLE IF EXISTS lookup;

CREATE TEMP TABLE lookup

select *

from (

VALUES

(0::int,-99999::numeric),

    (1::int, 100::numeric)

) as t (key, value);

You can also re-write this to use a CTE:

create temp table lookup 

as 

with t (key, value) as (

  values 

    (0::int,-99999::numeric), 

    (1::int,100::numeric)

)

select * from t;

Your Answer

Answer (1)

Ranjana

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;

5 Months