Re: Alter table x add constraing

Hi,
I have a table that inherits most of it's columns. Is this the best,
or at least a good, way to add a constraint? Is there a more
convenient (less typing) way?

--sql snippet------
--
-- Name: pi_contact; Type: TABLE; Schema: admin; Owner: kellert;
Tablespace:
--

CREATE TABLE pi_contact (
department VARCHAR(64),
organization VARCHAR(64),
)
INHERITS (contact);

ALTER TABLE admin.pi_contact ADD PRIMARY KEY
(admin.pi_contact.contact_id);
--
-- Name: TABLE pi_contact; Type: COMMENT; Schema: admin; Owner: kellert
--

COMMENT ON TABLE pi_contact IS 'contains PI contact info, inherits
from admin.contact';

-- end ----------------------

thanks,
Tom

Tom Keller, Ph.D.
kellert(at)ohsu(dot)edu
503-494-2442
6339b RJH (BSc)
http://www.ohsu.edu/research/core

On Fri, Sep 26, 2008 at 3:04 PM, Thomas Keller <kellert(at)ohsu(dot)edu> wrote:
> Hi,
> I have a table that inherits most of it's columns.

I have found that inheritance in PG is not worth the fact that indexes
are not inherited, and (thus) unique constraints are not inherited.
This means if you have a Researcher table with an ID field and inherit
it, you can a duplicate ID in the child tables.... usually bad.

I do it the old fashioned way: A Researcher table that contains all
the commonalities (id (or some PK), firstname, lastname, etc), and
then create non-inherited subtables that use a foreign key to this
Researcher table for attributes specific to certain types of
researchers. Use views if you need to see all researchers all at
once. Put a primary key on the Researcher table. (In the table
definition you can do it, you don't need an alter table -- read the
docs for the details.) You might have to type another 100 characters
or so, but in the longer term, 100 characters is almost nothing.

I have been bitten by this before, and I would recommend getting rid
of inheritance unless you need it for some other reason like
partitioning. It really isn't a great feature, though a lot of people
might disagree. If indexes were inherited, that would be a different
story.

-W