Recently on a testing infrastructure, to restore multiple tables (via copy command) I dropped all the foreign keys. I have restored this database schema dump into a different database before dropping the FK.

Is there any way that I can generate the create FK from information_schema or pg_constraint?

I tried to get it from a dump file, but the create table statement itself having the FK (not an Alter table statement).

Any suggestions?

1 Answer 1


I think you are looking for pg_constraint_def, like in the following example:

SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'child_accountid_itemid_fkey';
  • I think it gives a better idea, let me try.
    – TheDataGuy
    Commented Jul 6, 2020 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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