Menu
In postgres(9.4) I am trying to create a temporary table from select and apply "on commit drop" to same table. I am using below syntax.
CREATE TEMPORARY TABLE t5 ON COMMIT DROP AS select * from test4
It gives messages
Query returned successfully: 5 rows affected, 62 ms execution time.
But when i query the same table,
select * from t5
It throws error,
ERROR: relation "t5" does not exist
LINE 1: select * from t5
^
********** Error **********
ERROR: relation "t5" does not exist
SQL state: 42P01
Character: 15
Please let me know what is wrong with my understanding what is the mistake i am committing here.
Thanks
346
After creating the table, we can insert data into it as the persisted tables. At the same time, we can create a temporary table using the SQL SELECT INTO statement command.
Syntax to create PostgreSQL Temporary tables In the syntax, Specify the TEMP or TEMPORARY keyword after the CREATE keyword. Specify the list of columns with datatype after the name of the temp table.
In PostgreSQL, one can drop a temporary table by the use of the DROP TABLE statement. Syntax: DROP TABLE temp_table_name; Unlike the CREATE TABLE statement, the DROP TABLE statement does not have the TEMP or TEMPORARY keyword created specifically for temporary tables.
You cannot create a trigger or build an index or a view on a temporary object (@object). You cannot create triggers on system tables. If you try to create a trigger on a system table, Adaptive Server returns an error message and cancels the trigger.
You need to be in a transaction otherwise every single query is performed in its own transaction.
BEGIN; -- start transaction
CREATE TEMPORARY TABLE t5 ON COMMIT DROP AS select * from test4;
select * from t5;
COMMIT; -- drops the temp table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
