Postgres - create temp table with SELECT and ON COMMIT DROP

Question

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

Answer 1

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