2
  1. I have 2 tables. 2nd has foreign keys to reference 1st.
  2. 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.
  3. 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 
  • You can do the delete/insert in a single data-modifying CTE. Or declare the constraints as "deferred" then they are checked when you commit, not when you delete or insert – a_horse_with_no_name Jan 7 '16 at 23:42
  • @a_horse_with_no_name, deferred doesn't work for foreign keys – Vlad Jan 7 '16 at 23:43
  • @a_horse_with_no_name "delete/insert in a single data-modifying CTE" - how can I generate such script? pg_dump doesn't help much... – Vlad Jan 7 '16 at 23:44
  • 1
    Of course deferred works for foreign keys - that's what they were made for. If you need to restore a dump using pg_restore then the only thing I can think of is to drop the foreign key constraint, delete the rows, restore the table from the dump, add the foreign key constraint again. The dropping of the foreign keys can be automated. You just need to generate a script first that re-creates them. – a_horse_with_no_name Jan 7 '16 at 23:48
  • 1
    Whoever told you that is wrong: Quote from the manual "Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting" – a_horse_with_no_name Jan 7 '16 at 23:55
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.