Thanks, I missed this view!
Xavier Tarifa Departamento de Informática 972.397.020 - [email protected] 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 <[email protected]> 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? > > > >
