Re: inheritance

Perhaps, I am missing something in this whole inheritance picture... But
this is exactly one (of many) reasons why I could never understand why
people even consider using anything like inheritance in sql :-)
Perhaps, somebody could explain to me what kind of benefit you can get
from this 'inheritance' thing, that would outweight the obvious
disadvantages (such as this problem, a similar problem with
unique/foreign keys,  data duplication etc...).

For this particular case, I would do something like this, rather than
messing with inheritance:

create table cities
(
   name text primary key,
   population float,
   altitude int,
   capital bool not null default false
);

create view capitals as select name, population, altitude from cities
where capital;
create rule new_capital as on insert to capitals do instead insert into
cities values (new.*, true);
create rule upd_capital as on update to capitals do instead update
cities set name=new.name, population=new.population,
altitude=new.altitude where name=old.name;

-- plus, perhaps, a partial index to speed up getting a list of all
capitals if necessary:
create unique index capital_idx on cities (name) where capital;

Dima

Volker Krey wrote:

> Hello,
>
> I am working with PostgreSQL 7.2.1 under Windows 2000 (native version
> by PeerDirect) and have a problem with inheritance. To illustrate it,
> I will take the inheritance example from the Users Guide with a minor
> change, i.e. I introduce a PRIMARY KEY to the original table cities.
>
> CREATE TABLE cities (
>      name            text PRIMARY KEY,
>      population      float,
>      altitude        int     -- (in ft)
> );
>
> CREATE TABLE capitals (
>      state           char(2)
> ) INHERITS (cities);
>
> My problem now is the following: If I insert a data set into capitals,
> everything looks fine and a SELECT on cities returns the appropriate
> data just inserted into capitals. But if I now insert a city with the
> same name into cities the system will accept it so that I find myself
> with two entries in cities that have the same PRIMARY KEY. Of course
> this causes trouble, e.g. if I want to UPDATE one entry, an error
> message appears. If I still insist on changing the entry, both will be
> affected, because they share the same PRIMARY KEY.
> Can anybody tell me how to solve this problem? Maybe it has already
> been solved and is just a result of me using the old 7.2.1 version.
> I'd be very grateful for any hints, since the inheritance features of
> PostgreSQL would make life a lot easier for me.
>
> Thanks for your help, Volker.
>