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