Thread: Indexes and Inheritance

Thanks to Erik, Jeff, & Richard for their help.

I have a further inheritance question: do child tables inherit the indexes
created on parent columns, or do they need to be specified separately for
each child table? I.e., created via CREATE INDEX.

I assume at least that the implicit index created by a primary key would
inherit, but I don't know if that assumption is safe.

Thanks,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"

Keary Suska wrote:
> Thanks to Erik, Jeff, & Richard for their help.
>
> I have a further inheritance question: do child tables inherit the indexes
> created on parent columns, or do they need to be specified separately for
> each child table? I.e., created via CREATE INDEX.
>
> I assume at least that the implicit index created by a primary key would
> inherit, but I don't know if that assumption is safe.
>
> Thanks,
>
> Keary Suska
> Esoteritech, Inc.
> "Demystifying technology for your home or business"
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
No.  In addition, child tables do not inherit primary keys.  Think of it
like this: if you did a \d to describe a table that you were going to
use as a parent table in an inheritance chain, the child table would get
everything in the table listing the columns but nothing beneath the table.

--
erik jones <erik@myemma.com>
software development
emma(r)

Erik Jones <erik@myemma.com> writes:
> No.  In addition, child tables do not inherit primary keys.  Think of it
> like this: if you did a \d to describe a table that you were going to
> use as a parent table in an inheritance chain, the child table would get
> everything in the table listing the columns but nothing beneath the table.

Not quite.  CHECK constraints (and NOT NULL ones too) will be inherited.

The main reason we don't yet inherit indexes/unique constraints is that
the uniqueness would only be per-table, which is not what you'd expect.
Eventually someone will think of a way to fix that ...

            regards, tom lane

Keary Suska wrote:
> Thanks to Erik, Jeff, & Richard for their help.
>
> I have a further inheritance question: do child tables inherit the indexes
> created on parent columns, or do they need to be specified separately for
> each child table? I.e., created via CREATE INDEX.
>
> I assume at least that the implicit index created by a primary key would
> inherit, but I don't know if that assumption is safe.
>

In addition to what the others have replied, this is how i was told to
handle this (from this list):

-- create your parent table

CREATE TABLE parent_table (

    id    SERIAL PRIMARY KEY,
    this    VARCHAR(64) NOT NULL,
    that    VARCHAR(4) NOT NULL
);

-- create your child table(s)

CREATE TABLE child_table (

    foo    VARCHAR(64) NOT NULL,
    bar    VARCHAR(4) NOT NULL

) INHERITS (parent_table);

-- set the child table's id (from the parent) to take
-- the next value of the parent's SERIAL

ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
nextval('parent_table_id_seq');

-- now create an index on that (so that you have as many indexes
-- on the parent's SERIAL as child tables)

CREATE UNIQUE INDEX child_table_pk ON child_table (id);

Do those last two for each child table and then make sure that you
perform your INSERTs on the child table(s).

brian