When I try to execute
pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:
ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"
This is obviously due to the fact that I've emptied the data base before restoring it from the dump file (this file comes from a production database)... then of course no foreign keys constraint can be ok if one referenced table is empty...
Is there a way to disable the constraints and all foreign keys, for all tables, before I call
pg_restore.exe, and afterwards, re-enable the constraints and foreign keys.
In SO I found something interesting: deferring constraint checking to commit time.
But I don't think I can call
pg_restore.exe from inside
psql.exe after deferring the constraints.
There is also this post, dating back 10 years ago, suggesting to drop then re-add the constraints. Or to change the value of pg_class reltriggers to 0 and that would also be possible for constraints... but I'm afraid it is more hacking than good practice...
What do you advise, what is the best practice in this case ? Is using
pg_dump.exe with the
-clean flag creates a dump that bypasses the constraints checking when restoring the database ?