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?
