information_schema.constraint_column_usage view missing info

From: Xavier Tarifa <xavier(dot)tarifa(at)adparts(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: information_schema.constraint_column_usage view missing info
Date: 2026-08-24 06:44:13
Message-ID: CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello community,
how do you go about finding information about constraint columns? I
was trying to use the view information_schema.constraint_column_usage
but I've found out that to uniquely identify a constraint it would
need to show the constraint table, but in case of foreign keys it only
shows the table of the referenced column.
For example:

create schema proves;

create table proves.test (suc_pk int primary key);

create table proves.test1 (suc_fk integer);

create table proves.test2 (suc_fk integer);

alter table proves.test1 add constraint fk1 foreign key (suc_fk)
references proves.test;

alter table proves.test2 add constraint fk1 foreign key (suc_fk)
references proves.test;

then when I try to read the constraint the columns reference I can't
know distinguish the constraint on test1 from the constraint on test2:

select * from information_schema.constraint_column_usage
where constraint_name = 'fk1';

I guess I could look at the view definition and add use the same query
but adding the table constraint, but I don't know if these view
definitions might change with new postgres versions or not, I would
like something that I don't have to worry that it might stop working
in the future.
How would you go about it?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pierre Forstmann 2026-08-24 08:23:28 Re: information_schema.constraint_column_usage view missing info
Previous Message lakshmi 2026-08-19 05:15:54 Re: Extension - Pg_dbscanner: Intelligent Index Recommendation for PostgreSQL