Skip site navigation (1) Skip section navigation (2)

BUG #1006: information schema constraint information.

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1006: information schema constraint information.
Date: 2003-12-13 15:51:20
Message-ID: 20031213155120.AC3BECF8762@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2003-12-15 01:22:53
Subject: Re: BUG #1006: information schema constraint information.
Previous:From: Chris TraversDate: 2003-12-13 14:05:51
Subject: Re: Problems with initdb

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group