Thread: Inheritance in Postgresql ?

On Tue, Dec 15, 2009 at 11:36 PM, ABBAS SHAKEEL
<shakeel.abbas.qau@gmail.com> wrote:
> Hello
> I am not an expert for database but rarely design database for Web and IVR
> applications.
> Today I wonder if there is any practical advantage of inheritance of Tables.
> Lets take the same example that is commonly referred.
>
> CREATE TABLE cities (
>     name            text,
>     population      float,
>     altitude        int     -- in feet
> );
>
> CREATE TABLE capitals (
>     state           char(2)
> ) INHERITS (cities);
>
> Capital table inherit attributes from Cities but no data. I am confused with
> concept of inheritance in postgres.
>
> In These tables what i was supposing is inheritence as that in JAVA. but its
> seems to be  a different scene.

The child table inherits structure from the parent, the parent then
allows you to retrieve data from it and the children together.

On Wed, Dec 16, 2009 at 12:01 AM, ABBAS SHAKEEL
<shakeel.abbas.qau@gmail.com> wrote:
> Thanks Scott
> you are right . It inherits structure only and i don't know whether to call
> to it an advantage or disadvantage that it query the children table as well
> while select. But in case of insert and update this concepts takes a new
> turn.
> How inheritance effect data?
>  Can you please briefly compare concept of inheritance in Postgres and JAVA,
> as my mind is trying to compare this with already  stored concept in mind
> about inheritance ;)

I'm no Java specialist, so can't directly compare there.  In the case
of inserts those go to the parent or, if you insert directly to them
the child tables. Updates operate on the appropriate child tables.

By defining triggers or rules, you can make inserts to the parent
table go to appropriate child tables, which is often done for
partitioning data, which is in turn the most common use for table
inheritance.

You can also select data from JUST the parent with the only keyword,
as in "select * from only parent_table".

Note that pgsql supports multiple inheritance, so that you can have a
table inherit structure from two or more parent tables as well.

Note that as of 8.4, according to the docs:
"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique, primary key, and foreign key constraints) are not inherited."

So for foreign keys and such, inheritance is much less useful than
regular tables.

On Tue, Dec 15, 2009 at 11:33 PM, ABBAS SHAKEEL
<shakeel.abbas.qau@gmail.com> wrote:

> I feel relatively clear with this concept and i stopped my mind as well from
> comparing inheritance concept with JAVA  by saying that  inheritance concept
> in humans, in JAVA and in Postgres is different in many aspects.
> I would like to share an example as well which describe the advantage of
> inheritance in Postgres in detail . Example can be found here.

I developed a presentation the discusses hierarchical data modeling.
One section discusses PostgreSQL's inheritance.

http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug