- I have 2 tables. 2nd has foreign keys to reference 1st.
- I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and foreign keys.
- I need a generic way to do it for any table so no exact name of foreign keys or constraints or columns may be "hardcoded" (but script "generation" like pg_dump does is ok).
I can't just truncate 1st table and insert everything from the new version: pg doesn't allow to delete referenced rows.
I tried to use deferred like this (I planned to automatically append it to pg_dump generated sql):
START TRANSACTION; SET CONSTRAINTS ALL DEFERRED; DELETE FROM X; // ORIGINAL PART OF GENERATED SCRIPT INSERT INTO X VALUES (10, ....) COMMIT;
but I see
Key (id)=(10) is still referenced from table.
CONSTRAINT second_fkey FOREIGN KEY (X) REFERENCES first (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY