Skip site navigation (1) Skip section navigation (2)

Re: Need indexes on inherited tables?

From: Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar>
To: Phil Endecott <spam_from_postgresql_sql(at)chezphil(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Need indexes on inherited tables?
Date: 2004-06-28 16:30:16
Message-ID: 1088440216.910.153.camel@taz.oficina (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
you can find more information here: (under
the parameters section). For unique constraints, the only thing I can
think of is a table check constraint (SELECT count(pk)=0).

I agree with you, right now there are many inconveniences with
postgresql inheritance, that's why I never use inheritance this way. 
I usually stick to the "standard" way, for example, I create a people
table, and for every people I want to insert in my database there is one
row in this table, plus one row with the same id/pk (which is also a fk)
in a specialized table (let's say students, teachers, whatever). 
This simplifies things, because attribute
definitions/defaults/constraints are always in one table. Maybe you get
into some other troubles, but I find them less painfull :)

On Sat, 2004-06-26 at 19:30, Phil Endecott wrote:

> I asked if derived tables use primary key indexes generated in the base tables that they inherit from.
> Franco Bruno Borghesi replied:
> > [the derived table] is not inheriting any indexes, neither the
> > primary key constraint.
> OK, thanks!  I had come to the conclusion that it was not using the index, but I'm really surprised to hear that the primary key constraint that I define in a base table is not inherited.  Are any constraints inherited?  What happens if I declare a single-column primary key?  What if I declare a "not null" constraint or a "check" constraint in a base table?
> Having to replicate the constraints and indexes for each derived table is a pain - lots of error-prone typing - but there is a more serious problem: how can I ensure that these keys are unique across all of the derived tables?  (i.e. if T1 and T2 inherit from B, and B's primary key is (id,xx), then I want there to be at most one row in (T1 union T2) that has any value of (id,xx).)
> Is this a possible area for future enhancements?
> Regards,
> --Phil.

In response to

pgsql-sql by date

Next:From: Markus BertheauDate: 2004-06-28 17:06:17
Subject: Re: Empty array IS NULL?
Previous:From: Joe ConwayDate: 2004-06-28 16:26:27
Subject: Re: Empty array IS NULL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group