Re: [SQL] pg primary key bug?

[pginfo]

Hi,

No, I do not inherited tables.

The result of this query is:
01=# SELECT tableoid::regclass, *
01-# FROM a_constants_str
01-# WHERE constname = 'DOCPLAID';
    tableoid     | constname | fid | constvalue
-----------------+-----------+-----+------------
 a_constants_str | DOCPLAID  |   0 | SOF_19738
 a_constants_str | DOCPLAID  |   0 | SOF_19738
(2 rows)

regards,
ivan.


Michael Fuhr wrote:

On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:

  

01=# select * from a_constants_str where constname='DOCPLAID' ;
constname | fid | constvalue
-----------+-----+------------
DOCPLAID  |   0 | SOF_19738
DOCPLAID  |   0 | SOF_19738
(2 rows)
    

Do you have any inherited tables?  What's the result of the following
query?

SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';

Inherited tables are documented to have deficiencies regarding
constraints.  Observe:

CREATE TABLE parent (
    constname   varchar(30) NOT NULL,
    fid         integer NOT NULL,
    constvalue  varchar(30),
    PRIMARY KEY (constname, fid)
);  
 
CREATE TABLE child () INHERITS (parent);

INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');

INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR:  duplicate key violates unique constraint "parent_pkey"

INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');

SELECT tableoid::regclass, * FROM parent;
 tableoid | constname | fid | constvalue 
----------+-----------+-----+------------
 parent   | DOCPLAID  |   0 | SOF_19738
 child    | DOCPLAID  |   0 | SOF_19738
(2 rows)