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 (
<http://forums.devarticles.com/archive/t-4364> 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, '
<http://forums.devarticles.com/archive/t-4364> 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, ' <http://forums.devarticles.com/archive/t-4364> 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