Re: Indexes and Inheritance

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