PostgreSQL: constraint name is not necessarily unique

Question

I'm trying to list all the foreign keys in a schema, and, for each key, listing the columns involved. So I'm querying the pg_catalog.pg_constraint and information_schema.columns tables.

I need a way to distinguish the keys, to do the second query and retrieve the key columns list.

I thought to use the constraint name (the conname column, in pg_catalog.pg_constraint table), but the PostgreSQL documentation about pg_constraint says that the constraint name is not necessarily unique! Why? I can't find additional information about this fact in the documentation.

Is the couple connamespace + conname unique?

This are my queries:

Retrieve the lists of foreign keys from and to the given schema:

SELECT
conname AS constraint_name,
conrelid::regclass AS referring_table, 
confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint
WHERE contype = 'f' AND ((conrelid::regclass || '' LIKE '<my_schema_name>.%') OR (confrelid::regclass || '' LIKE '<my_schema_name>.%'))

Retrieve the list of columns of a given key:

SELECT
c.column_name AS key_column
FROM information_schema.columns c
JOIN pg_catalog.pg_constraint r ON (c.table_schema || '.' || c.table_name) = (r.conrelid::regclass || '')
WHERE r.conname = '<my_constraint_name>'

Thanks for your help.

Answer 1

Constraint names are unique only within the object on which they are defined.

Two different tables (or domains) can have constraints with the same name.