Message-ID: <313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com>
Date: Mon, 24 Aug 2026 10:23:28 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: information_schema.constraint_column_usage view missing info
To: Xavier Tarifa <xavier.tarifa@adparts.com>,
 pgsql-general@lists.postgresql.org
References: 
 <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>
Content-Language: en-US
From: Pierre Forstmann <pierre.forstmann@gmail.com>
In-Reply-To: 
 <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/313a3a99-0131-49d5-a372-cc1dfbe7bb69%40gmail.com>
Precedence: bulk

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?
>
>