I'm porting a postgres client app (along with all the users'
databases) from postgres 7.1.3 to 7.3.x. The new version of the app
needs a way of enumerating foreign key constraints on a table,
including the constraint names and the constrained columns.
Unfortunately, postgres 7.1.3 didn't have a pg_constraint table, and
the dump/restore process doesn't create the appropriate pg_constraint
rows for foreign keys when migrating. With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?
It looks like I can get a list of constraint names from the pg_trigger
table, but each foreign key constraint corresponds to several triggers
on either of the two tables involved. How can I distinguish the
referenced table from the referencing one? Is pg_trigger even the
correct place to look?
Once I find a migrated foreign key constraint, since it won't have an
entry in pg_constraint, will I be able to drop it?
