How to create a postgresql temporary table?

561 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;

How to create a postgresql temporary table?

Your Answer