Tom Kazimiers <tom@xxxxxxxxxxxxxxx> writes: > I am on Postgres 13 and have a problem with updates in a CTE. While certainly > not generally recommended, I need to update the primary key in a table that is > referenced by a few other tables. The table definition is attached to the end > of this email [2]. I'd like to avoid dropping and recreating the constraints or > even columns, because these tables can become quite large. While I could define > the FK constraints as ON UPDATE CASCADE, I wonder why an alternative solution > using a CTE doesn't work: I tried to reproduce your problem and failed; the attached script acts as I'd expect. So there must be some moving part you've not mentioned. Can yo create a similar self-contained example that fails? regards, tom lane
drop table if exists pt, c1, c2; create table pt (id int primary key); create table c1 (id int primary key, ref int references pt deferrable initially deferred); create table c2 (id int primary key, ref int references pt deferrable initially deferred); insert into pt values(11); insert into c1 values(1, 11); insert into c2 values(2, 11); update pt set id = 12 where id = 11; -- fails with u1 as ( update c1 set ref = 12 where ref = 11 ) update pt set id = 12 where id = 11; -- fails with u1 as ( update c1 set ref = 12 where ref = 11 ), u2 as ( update c2 set ref = 12 where ref = 11 ) update pt set id = 12 where id = 11; -- ok
