agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedFrom: Pierre Forstmann <pierre.forstmann@gmail.com>
To: Xavier Tarifa <xavier.tarifa@adparts.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: information_schema.constraint_column_usage view missing info
Date: Mon, 24 Aug 2026 10:23:28 +0200
Message-ID: <313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com> (raw)
In-Reply-To: <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>
References: <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>
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?
>
>
view thread (4+ messages) latest in thread
Message-ID: <313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com>
Permalink: ../313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com/
Also on: postgresql.org/message-id/313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: pierre.forstmann@gmail.com, xavier.tarifa@adparts.com, pgsql-general@lists.postgresql.org
Subject: Re: information_schema.constraint_column_usage view missing info
In-Reply-To: <313a3a99-0131-49d5-a372-cc1dfbe7bb69@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox