Get information about foreing key from PostgreSQL

Question

There is this answer but the information is not complete.

I'm trying to get the information whether the foreign key is "MATCH FULL", "MATCH PARTIAL" or "MATCH SIMPLE".

Which table/field this information is stored?

Answer 1

The column pg_catalog.pg_constraint.confmatchtype (in the table pg_constraint in the schema pg_catalog) has the information you're looking for. It's just one character:

• 'f' for MATCH FULL
• 's' for MATCH SIMPLE and
• 'p' for MATCH PARTIAL.

Foreign key constraints are the ones with pg_catalog.pg_constraint.contype = 'f' BTW. Only those have a confmatchtype <> ' ' (for obvious reasons).

See also "51.13. pg_constraint".