PostgreSQL inheritance surprises
I've been trying t exploit inherits in PostgreSQL in a OO way with no success at this moment, meanwhile I came out with this code that underline the "surprises" you may have when you use inherits.
To avoid flame wars, this is not a critic to PG. This is documented in the manual even if I didn't find it easy to understand maybe due to my OO prejudices and I'm not aware of any open source, production ready DB that implement it as I would expect.
Another thing that is not evidenced by the code below, that is halfway between the RDBMS world and the OO world, is the fact that in DB world definition=instantiation, so you can't have "virtual" parents and this comes with other generally unwelcome side effects.
But code is worth a thousand worlds. This code was tested on 7.4.13, but 8.1 seems to behave similarly.
\c template1
drop database test;
create database test;
\c test
create table T_List (idList serial, Name varchar(31));
alter table T_List add constraint PK_List primary key (idList);
create table T_Item (idItem serial, idList int, Name varchar(31));
alter table T_Item add constraint PK_Item primary key (idItem);
alter table T_Item add constraint FK_List foreign key (idItem) references T_List(idList) on delete cascade;
create table T_Item0 (Field0 varchar(31)) inherits (T_Item);
create table T_Item1 (Field1 varchar(31)) inherits (T_Item);
insert into T_List (Name) values('list1');
insert into T_List (Name) values('list2');
/*
-- these will work!
insert into T_Item0 (idList, Name, Field0) values(0,'list 0 item0 0', 'field0 item0 0');
insert into T_Item0 (idList, Name, Field0) values(0,'list 0 item0 0', 'field0 item0 1');
*/
insert into T_Item0 (idList, Name, Field0) values(1,'list 1 item0 0', 'field0 item0 0');
insert into T_Item0 (idList, Name, Field0) values(1,'list 1 item0 0', 'field0 item0 1');
insert into T_Item0 (idList, Name, Field0) values(2,'list 2 item0 0', 'field0 item0 0');
insert into T_Item0 (idList, Name, Field0) values(2,'list 2 item0 0', 'field0 item0 1');
insert into T_Item1 (idList, Name, Field1) values(1,'list 1 item1 0', 'field1 item1 0');
insert into T_Item1 (idList, Name, Field1) values(1,'list 1 item1 0', 'field1 item1 1');
insert into T_Item1 (idList, Name, Field1) values(2,'list 2 item1 0', 'field1 item1 0');
insert into T_Item1 (idList, Name, Field1) values(2,'list 2 item1 0', 'field1 item1 1');
-- where did uniqueness go?
insert into T_Item0 values(1,1,'list0 item0 0', 'it shold fail');
select * from T_List;
select * from T_Item;
select * from T_Item1;
select * from only T_Item;
select * from only T_Item1;
delete from T_List where idList=1;
select * from T_List;
select * from T_Item;
select * from T_Item1;
select * from only T_Item;
select * from only T_Item1;
delete from T_Item where idItem=1;
select * from T_List;
select * from T_Item;
select * from T_Item1;
select * from only T_Item;
select * from only T_Item1;
It seems to me that at this stage inherits is just a tool to have "special" unions and avoid typing.
You can find a really useful exaple of the use of inherits at Table Inheritance Overview.
Looking at gnumed code may give you some further inspiration
look at:
- gmAudit*.sql and gmclinical.sql in gnumed/gnumed/server/sql
- gmAuditSchemaGenerator.py in gnumed/gnumed/server/bootstrap/
Thanks to Karsten Hilbert that helped me to clear my mind a bit.