After doing some research, this is apparently a
known long-standing issue with inheritance in Postgres. Nobody calls it a
"bug" outright, though. Just a limitation. It makes me wonder how
others have tackled problems of a similar nature.
I've since devised a different way
(http://archives.postgresql.org/pgsql-general/2003-05/msg00585.php), but if you
guys have any other suggestions for how you've handled this sort of thing in
PostgreSQL, let me know.
To grossly simplify, here's what I was trying to do.
CREATE TABLE products (
id serial NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
price numeric(6,2)
);
CREATE TABLE pants (
waist smallint,
length smallint,
colour varchar(12)
) inherits (products);
CREATE TABLE computers (
cpu
varchar(12),
mhz smallint,
) inherits (products);
INSERT INTO pants (name,price,waist,length,colour)
VALUES ('Brand-X Cargo Pants', 49.95, 32, 34, 'khaki');
INSERT INTO computers (name,price,cpu,mhz)
VALUES ('Flower Power iMac', $666.66, 'Motorola
750', 500);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 2 Flower Power iMac
INSERT INTO computers (id, name,price,cpu,mhz)
VALUES (1, 'Mac
Plus', $5.00, 'Motorola 68000', 8);
SELECT id,name FROM products;
--> 1 Brand-X Cargo Pants
--> 1 Mac Plus
--> 2 Flower Power iMac
Huh? But products.id is a primary key! The "Mac Plus" screwed it up
by inserting duplicate values!
Thanks for all
John Luise
