Re: information_schema.constraint_column_usage view missing info

Hello;

Try using INFORMATION_SCHEMA.KEY_COLUMN_USAGE:

CREATE SCHEMA
create table proves.test (suc_pk int primary key);
CREATE TABLE
create table proves.test1 (suc_fk integer);
CREATE TABLE
create table proves.test2 (suc_fk integer);
CREATE TABLE
alter table proves.test1 add constraint fk1 foreign key (suc_fk)
references proves.test;
ALTER TABLE
alter table proves.test2 add constraint fk1 foreign key (suc_fk)
references proves.test;
ALTER TABLE
select * from information_schema.constraint_column_usage
where constraint_name = 'fk1';
 table_catalog | table_schema | table_name | column_name |
constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 pierre        | proves       | test       | suc_pk      | pierre     
       | proves            | fk1
 pierre        | proves       | test       | suc_pk      | pierre     
       | proves            | fk1
(2 rows)

select * from information_schema.key_column_usage
where constraint_name = 'fk1';
 constraint_catalog | constraint_schema | constraint_name |
table_catalog | table_schema | table_name | column_name |
ordinal_position | position_in_unique_constraint
--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------
 pierre             | proves            | fk1             | pierre     
  | proves       | test1      | suc_fk      |         1 |             
               1
 pierre             | proves            | fk1             | pierre     
  | proves       | test2      | suc_fk      |         1 |             
               1
(2 rows)

Le 24/08/2026 à 08:44, Xavier Tarifa a écrit :
> 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?
>
>