On Wed, Jan 10, 2024 at 12:29:54PM +0100, Torsten Förtsch wrote: > Hi, > > imagine a simple table with 1 row > > =# table tf; > i | x > ---+---- > 1 | xx > (1 row) > > And this query: > > with x as (update tf set i=i+1 returning *) > , y as (update tf set x=x||'yy' returning *) > select * from x,y; > > My PG14 gives this result > > i | x | i | x > ---+---+---+--- > (0 rows) > > To me that was a bit surprising. I would have expected it to fail with > something like "can't update the same row twice in the same command". > > If I check the table content after the query I see the i=i+1 part was executed. > > Is this expected behavior? Yes, this surprised me too. Here is a reproducible case: CREATE TABLE tf (i INT, x TEXT); INSERT INTO tf VALUES (1, 'x'); WITH x AS (UPDATE tf SET i=i+1 RETURNING *), y AS (UPDATE tf SET x=x||'yy' RETURNING *) SELECT * FROM x,y; i | x | i | x ---+---+---+--- TABLE tf; i | x ---+--- 2 | x I know you can cascade the returning of one table into the update of another table, but maybe it doesn't work into the same table. -- Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
