BUG #1006: information schema constraint information. - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1006
Logged by:          Majolee InfoTech
Email address:      info@majolee.info
PostgreSQL version: 7.4
Operating system:   Redhat 9.0
Description:        information schema constraint information.
Details:

Hello,

as per the documentation of information schema (constraint_column_usage) view should return exact column names for a
constraintcreated.  

Currently this view has a bug for foreign key constraints created for a table for more than one times. It shows first
insertedcolumn name for all of the following foreign keys defined for same table. 
#####################################################
CREATE TABLE public.test
(
  fld1 varchar(25) NOT NULL,
  fld2 varchar(25),
  fld3 varchar(25),
  CONSTRAINT pk1 PRIMARY KEY (fld1)
) WITH OIDS;
CREATE TABLE public.test2
(
  pk2 int8 NOT NULL,
  fk1 varchar(25),
  CONSTRAINT pk22 PRIMARY KEY (pk2),
  CONSTRAINT fk11 FOREIGN KEY (fk1) REFERENCES public.test (fld1) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITH OIDS;
CREATE TABLE public.test3
(
  fld_1 varchar(25) NOT NULL,
  fld_2 varchar(25) NOT NULL,
  fld_3 varchar(25) NOT NULL,
  CONSTRAINT pk3 PRIMARY KEY (fld_1),
  CONSTRAINT fk3_1 FOREIGN KEY (fld_2) REFERENCES public.test (fld1) ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fk3_2 FOREIGN KEY (fld_3) REFERENCES public.test2 (pk2) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITH OIDS;
#####################################################

This on querying
#####################################################
select * from information_schema.constraint_column_usage
#####################################################

gives following output

#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 ERP           | public       | test       | fld1        | ERP                | public            | pk1
 ERP           | public       | test2      | pk2         | ERP                | public            | pk22
 ERP           | public       | test2      | pk2         | ERP                | public            | fk11
 ERP           | public       | test3      | fld_1       | ERP                | public            | pk3
 ERP           | public       | test3      | fld_1       | ERP                | public            | fk3_1
 ERP           | public       | test3      | fld_1       | ERP                | public            | fk3_2
#####################################################

Which should show (Changes displayed within *CHANGE*)

#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 ERP           | public       | test       | fld1        | ERP                | public            | pk1
 ERP           | public       | test2      | pk2         | ERP                | public            | pk22
 ERP           | public       | test2      | *fk1*         | ERP                | public            | fk11
 ERP           | public       | test3      | fld_1       | ERP                | public            | pk3
 ERP           | public       | test3      | *fld_2*       | ERP                | public            | fk3_1
 ERP           | public       | test3      | *fld_3*       | ERP                | public            | fk3_2
#####################################################

Please update us on the same.

Thanks.....

Majolee InfoTech