Replace content of only one table with new version (should disable foreign keys?) 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.

The constraint:

CONSTRAINT second_fkey FOREIGN KEY (X) REFERENCES first (id) MATCH SIMPLE 
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY
The deferred constraints solution (see an example in the question) actually works if I make the keys deferrable in normal way. 

This didn't work because I did UPDATE pg_catalog.pg_constraint SET condeferrable = true; but forgot to set the same for pg_catalog.pg_trigger.