Question 88963 in PostgreSQL Question posted in
The official documentation can be found here.

Error after second read from CTE or TEMPORARY TABLE in Postgres – Postgresql

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    You can certainly use a CTE (with …) once. However you used it incorrectly. A CTE is essentially a view which exists only for the duration of the query where it is created. In query the semi-colon (;) after the update terminated the statement and thus the CTE itself goes out-of-scope. So for theselect statement it no longer exists. Postgres allows DML in a CTE so to correct your statement create a second CTE that does the update. So:

    with unwatched_rows as (
        select id 
          from my_table 
         where is_readed = false for update
     )
   , upt as (
       update my_table 
          set is_readed = true 
        where id in (select id from unwatched_rows)
     ) 
select * 
  from unwatched_rows;

    As far as wrapping into a function this is actually quite simple. It does not require a temp table not, for that matter, a CTE. It reduces to a single SQL statement:

    create or replace function unwatched_rows()
  returns setof my_table.id%type 
 language sql 
as $$ 
    update my_table 
       set is_readed = true
     where not is_readed    -- same as: is_readed = false
    returning id;
$$;

    See here for demo illustrating both.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search