Re: inheritance - Mailing list pgsql-novice

This brings up a related question.  What if a non-capital city becomes
a capital?  Maybe Illinois will decide to make Chicago its capital
instead of Springfield :-)   With Dmitry's method you just set the
boolean flag.  How do you do that with inherited tables?  Do you have
to delete the record from the cities table and create a new one in the
capitals table?  This will may break relationships.

Avi

On Wednesday, Jul 9, 2003, at 11:03 America/Chicago, Jake Stride wrote:

> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly