Re: information_schema.constraint_column_usage view missing info

Thanks, I missed this view!

Xavier Tarifa
Departamento de Informática
972.397.020 - xavier(dot)tarifa(at)adparts(dot)com

AD PARTS, SL
Av. Mas Vilà, 137-149. Riudellots de la Selva.
http://www.adparts.com

Aviso de confidencialidad

On Mon, 24 Aug 2026 at 10:23, Pierre Forstmann
<pierre(dot)forstmann(at)gmail(dot)com> wrote:
>
> 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?
> >
> >