2
CREATE table test (
    id integer,
    x integer
);

INSERT INTO test VALUES (1, 1);

SELECT works:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
SELECT id FROM test WHERE id IN (SELECT id FROM res);

id 
---
 1
(1 row)

But DELETE does not work:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
DELETE FROM test WHERE id IN (SELECT id FROM res);

DELETE 0
2

Common table expressions (CTE) of the same statement are all based on the same snapshot of the database. I.e. all sub-statements see the same state of underlying tables.

Your DELETE tries to modify the same row(s) that your UPDATE already modifies. The manual addresses your case exactly:

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

Bold emphasis mine.

  • Thank you very much. I will read the documentation. – Igor Che Aug 9 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.