I saw a question at a StackOverflow site:
http://dba.stackexchange.com/questions/151199/why-cant-rows-inserted-in-a-cte-be-updated
regarding statements with CTEs that mofify a table twice, with an insert
and then an update:
(post:)
---------------------------------------------------------------------------
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 │└────┴─────┘(1 row)
---------------------------------------------------------------------------
I answered that this is unpredictable behaviour but the docs state only the
update-update and update-delete cases explicitly and the general wording is
about 2 updates.
I suggest that the insert-update and insert-delete cases are added as well
(assuming that my understanding is correct and that these also result in
unpredictable results).
Pantelis Theodosiou
