CODEWITHSUNDEEP
sqlpostgresql
Oto Shavadze
Oto Shavadze

Reputation: 42753

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

?

Upvotes: 1

Views: 891

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 5

Related Questions