Error after second read from CTE or TEMPORARY TABLE in Postgres

April 17, 2023

I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction.

I have tried two options. First with CTE:

BEGIN;
WITH unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
)
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

In this case I get an error relation "unwatched_rows" does not exist. How I learned this is because i can’t use CTE more than once. And as I understand it, CTE is not suitable for my situation

Second option with CREATE TEMPORARY TABLE:

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

I am running this query through DBeaver. For first time it works fine, but when I try to run this query again I get an error relation "unwatched_rows" already exists. I don’t understand why this is happening. Shouldn’t the temporary table be dropped right after the commit?
What am I doing wrong and how to solve it?

>Solution :

If you want the temporary table to be removed right after the commit, You will have to specify ON COMMIT DROP when create it :

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows ON COMMIT DROP AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

An other solution is to drop your temporary table before created it again :

BEGIN;
DROP TABLE IF EXISTS unwatched_rows;
CREATE TEMPORARY TABLE unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;