Inherited tables

Inherited tables

Post by Lucio Benfant » Sun, 31 Dec 1899 09:00:00

I'm trying to inherit primary key constraint from a table.
I'm using DS/UDO 9.14.UC4.
The SQL code is something like this:

  (PRIMARY KEY (name));

CREATE TABLE CatTable OF TYPE CatTable_t UNDER Tables;

  1. Tables_t is a row type having a field named "name"
  2. CatTable_t is a sub-type of Tables_t

Reading the documentation I supposed to have a primary key on the name
field in the CatTable table too, but that doesn't happen!!
I'm wrong or something in the server is wrong?

Thanks in advance
Best regards

  Lucio Benfante

Sent via
Share what you know. Learn what you don't.


1. Inherited tables vs non-inherited tables

I've been trying an inherited-table schema for my database and seem to
be getting a performance hit. The old table looked like this:

CREATE TABLE center_out (
    subject                     text,
    arm                         char,
    target                      int4,
    rep                         int4,
    success                     int2,       -- end of primary key
    exp_date                    date,
    exp_time                    time,  
    inter_target_radius         int4,
    target_radius               int4);

Since the fields subject, arm, target, and rep appeared in just about
every other table as the primary key, I made it an inherited table for
the new schema:

CREATE TABLE center_out (
        subject    text,
        arm        char,
        target     int2,
        rep        int4,
        success    int2    

CREATE UNIQUE INDEX pkcenter_out ON center_out (subject, arm, target,
rep, success);

CREATE TABLE center_out_behavior (
    exp_date                     date,
    exp_time                     time,
    inter_target_radius          int2,
    target_radius                int2
) INHERITS (center_out);

However, queries such as "SELECT DISTINCT subject FROM center_out"
seem to take 2-3 times longer in the new schema. Does this make sense?
I was hoping that the inherited information would lead to an
easier-to-follow, smaller, and faster database since there wouldn't be
redundant information across tables.


 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

2. Certificates and chipcards

3. Inherited tables and NOT NULL (pg 7.2.1)

4. Update using CASE

5. Inherited tables....

6. Oracle Application Version 11i

7. getting inherited table name

8. Blocked by -1

9. plpgsql, fk inherited tables

10. references constraint on inherited tables?

11. Problem with referential integrity and inherited tables

12. Problem with referential integrity and inherited tables in 7.1.1

13. foreign keys with inherited tables