CTEs modifying the same table more than once
От | Pantelis Theodosiou |
---|---|
Тема | CTEs modifying the same table more than once |
Дата | |
Msg-id | CAE3TBxwvO33+uU1X3Q5zWb64KXbQtSHVBN2LB82NrHRmrp8oag@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: CTEs modifying the same table more than once
|
Список | pgsql-docs |
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:http://dba.stackexchange.com/questions/151199/why-cant-rows-inserted-in-a-cte-be-updated
(post:)
---------------------------------------------------------------------------
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.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)
---------------------------------------------------------------------------В списке pgsql-docs по дате отправления: