BUG #1006: information schema constraint information.

The following bug has been logged online:

Bug reference: 1006
Logged by: Majolee InfoTech
Email address: info(at)majolee(dot)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 constraint created.

Currently this view has a bug for foreign key constraints created for a table for more than one times. It shows first inserted column 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