CODEWITHSUNDEEP
postgresql
Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2725

create temp table that deletes itself when I end my transaction

I am using Postgres 9.6

I wish to CREATE temp table that will drop itself at the end of the transaction. I have been looking through the documentation and am having a hard time locating the answer to this.

Upvotes: 2

Views: 2587

Answers (2)

Venryx
Venryx

Reputation: 17999

Like this:

BEGIN;

CREATE TEMP TABLE my_temp(id) ON COMMIT DROP AS VALUES
  ('SOME_ID_1'),
  ('SOME_ID_2'),
  ('SOME_ID_3');

-- put your query that uses the temp-table here
SELECT * FROM nodes
JOIN my_temp ON nodes.id = my_temp.id;

END;

Upvotes: 1

user330315
user330315

Reputation:

Quote from the manual

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:
...
DROP
The temporary table will be dropped at the end of the current transaction block.

(emphasis mine)

The "end of transaction block" is defined by a commit or a rollback.

Upvotes: 3

Related Questions