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