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  (Marko Tiikkaja <marko@joh.to>)
Список pgsql-docs
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

В списке pgsql-docs по дате отправления:

Предыдущее
От: Euler Taveira
Дата:
Сообщение: Re: Translation to Portuguese
Следующее
От: Marko Tiikkaja
Дата:
Сообщение: Re: CTEs modifying the same table more than once