Skip to content
Advertisement

How to create empty temporary table?

Need to create empty temporary table, but that not depends any query result yet, but I need only structure. (then, according to some logic, will be different data inserted into this).

begin;
CREATE TEMP TABLE my_tmp_table ON COMMIT DROP (
    col1 int,
    col2 numeric,
    col3 text
);
...

throws syntax error

So is this way (which looks bit “tricky”) only correct way:

begin;
CREATE TEMP TABLE my_tmp_table ON COMMIT DROP   
AS 
SELECT 
1::INT as col1, 
1::numeric as col2, 
''::text as col3
WHERE false;
...

?

Advertisement

Answer

As documented in the manual the ON COMMIT option needs to be after the table definition:

CREATE TEMP TABLE my_tmp_table 
(
  col1 int,
  col2 numeric,
  col3 text
)
ON COMMIT DROP; --<< here
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement