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

My guess is that the temp table is being dropped as soon as the select finishes, when it also has finished just being created. What exact behavior are you looking for here? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 18, 2018 at 10:22
As Tim said: you probably have autocommit enabled and thus the create table statement is automatically committed and thus the temp table is dropped — user330315
– user330315, Commented Sep 18, 2018 at 10:23

Eelke · Accepted Answer · 2018-09-18 10:24:02Z

31

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

answered Sep 18, 2018 at 10:24

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

usersam Over a year ago

Yes @Eleke, you are right. This works when i put in a block. Thanks

Collectives™ on Stack Overflow

Postgres - create temp table with SELECT and ON COMMIT DROP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related