Re: inheritance - Mailing list pgsql-novice

Doing things your way means that you end up having several different
views and sets of data to look after on updates etc.

The example given means that you only have two tables and can quite
easily see all the cities at once, and only the capitals if you want.

You don't have to worry about rules and extra data, postgreSQL looks
after everything for you. With the inheritance, anything you insert into
the capitals table automatically appears in the the cities table too, so
do all updates.

Hope this explains :-)

As to duplicate keys, you can recreate the primary key in each inherited
table. Also shouldn't you be updating in the relavent table?

On Wed, 2003-07-09 at 16:54, Dmitry Tkach wrote:
> 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.
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend