PostgreSQL CTE – Why Can’t Rows Be Updated in Same Statement?

ctepostgresql

In PostgreSQL 9.5, given a simple table created with:

create table tbl (
    id serial primary key,
    val integer
);

I run SQL to INSERT a value, then UPDATE it in the same statement:

WITH newval AS (
    INSERT INTO tbl(val) VALUES (1) RETURNING id
) UPDATE tbl SET val=2 FROM newval WHERE tbl.id=newval.id;

The result is that the UPDATE is ignored:

testdb=> select * from tbl;
┌────┬─────┐
│ id │ val │
├────┼─────┤
│  1 │   1 │
└────┴─────┘

Why is this? Is this limitation part of the SQL standard (i.e. present in other databases), or something specific to PostgreSQL that might be fixed in future? The WITH queries documentation says multiple UPDATEs are not supported, but does not mention INSERTs and UPDATEs.

Best Answer

All sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database.

The UPDATE sees the same state of the underlying table as the INSERT, which means the row with val = 1 is not there, yet. The manual clarifies here:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

Each statement can see what's returned by another CTE in the RETURNING clause. But the underlying tables look all the same to them.

You would need two statements (in a single transaction) for what you are trying to do. The given example should really just be a single INSERT to begin with, but that may be due to the simplified example.

Related Question