Re: Updating PK and all FKs to it in CTE

Tom Kazimiers <tom(at)voodoo-arts(dot)net> 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