agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedinformation_schema.constraint_column_usage view missing info
4+ messages / 3 participants
[nested] [flat]
* information_schema.constraint_column_usage view missing info
@ 2026-08-24 06:44 Xavier Tarifa <xavier.tarifa@adparts.com>
0 siblings, 2 replies; 4+ messages in thread
From: Xavier Tarifa @ 2026-08-24 06:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: information_schema.constraint_column_usage view missing info
@ 2026-08-24 08:23 Pierre Forstmann <pierre.forstmann@gmail.com>
parent: Xavier Tarifa <xavier.tarifa@adparts.com>
1 sibling, 1 reply; 4+ messages in thread
From: Pierre Forstmann @ 2026-08-24 08:23 UTC (permalink / raw)
To: Xavier Tarifa <xavier.tarifa@adparts.com>; pgsql-general@lists.postgresql.org
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?
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: information_schema.constraint_column_usage view missing info
@ 2026-08-24 09:25 Xavier Tarifa <xavier.tarifa@adparts.com>
parent: Pierre Forstmann <pierre.forstmann@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Xavier Tarifa @ 2026-08-24 09:25 UTC (permalink / raw)
To: Pierre Forstmann <pierre.forstmann@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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:
>
> 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?
> >
> >
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: information_schema.constraint_column_usage view missing info
@ 2026-08-24 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Xavier Tarifa <xavier.tarifa@adparts.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2026-08-24 14:23 UTC (permalink / raw)
To: Xavier Tarifa <xavier.tarifa@adparts.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-08-24 14:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-24 06:44 information_schema.constraint_column_usage view missing info Xavier Tarifa <xavier.tarifa@adparts.com>
2026-08-24 08:23 ` Pierre Forstmann <pierre.forstmann@gmail.com>
2026-08-24 09:25 ` Xavier Tarifa <xavier.tarifa@adparts.com>
2026-08-24 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox