information_schema.constraint_column_usage view missing info

Started by Xavier Tarifa3 months ago4 messagesgeneral
Jump to latest
#1Xavier Tarifa
xavier.tarifa@adparts.com

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?

#2Pierre Forstmann
pierre.forstmann@gmail.com
In reply to: Xavier Tarifa (#1)
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 :

Show quoted text

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?

#3Xavier Tarifa
xavier.tarifa@adparts.com
In reply to: Pierre Forstmann (#2)
Re: information_schema.constraint_column_usage view missing info

Thanks, I missed this view!

Xavier Tarifa
Departamento de Informática
972.397.020 - xavier.tarifa@adparts.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.forstmann@gmail.com> wrote:

Show quoted text

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?

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Xavier Tarifa (#1)
Re: information_schema.constraint_column_usage view missing info

Xavier Tarifa <xavier.tarifa@adparts.com> writes:

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.

Yeah. The back story here is that the SQL standard says that
constraint names are to be unique per-schema. So they defined
information_schema.constraint_column_usage on the assumption
that constraint_schema + constraint_name is a unique identifier.
Postgres of course considers constraint names to be local to a
table, so that different tables in the same schema can use the
same constraint name. So you can either design your app to
obey the spec's restriction, or use something other than
constraint_column_usage to get info about your constraints.

Neither end of this is likely to change. We're not going to
extend constraint_column_usage with extra columns: the entire
point of information_schema is to present an exactly
standards-compliant view of the catalogs. And we're not going
to require constraint names to be unique per-schema either;
even if we wanted to do that, it'd doubtless break too many
existing applications.

regards, tom lane