Inheritance and column references problem

From: Scott Goodwin <scott(at)scottg(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Inheritance and column references problem
Date: 2004-02-25 00:03:35
Message-ID: 0E847D2A-6726-11D8-9D13-000A95A0910A@scottg.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The following SQL:

create table toinherit (
id integer primary key
);

create table leftside (
leftname varchar(64) not null unique
) inherits (toinherit);

create table rightside (
rightname varchar(64) not null unique
) inherits (toinherit);

create table linkthem (
left_id integer references toinherit (id),
right_id integer references toinherit (id)
);

insert into leftside (id, leftname) values (1, 'leftname1');
insert into rightside (id, rightname) values (2, 'rightname2');
insert into linkthem (left_id, right_id) values (1, 2);

...gives me this error:

CREATE TABLE
CREATE TABLE
INSERT 55919 1
INSERT 55920 1
psql:without_inherit.sql:24: ERROR: insert or update on table
"linkthem" violates foreign key constraint "$1"
DETAIL: Key (left_id)=(1) is not present in table "toinherit".

If I do the same thing but without using inheritance:

create table toinherit (
id integer primary key
);

create table leftside (
id integer references toinherit (id),
leftname varchar(64) not null unique
);

create table rightside (
id integer references toinherit (id),
rightname varchar(64) not null unique
);

create table linkthem (
left_id integer references toinherit (id),
right_id integer references toinherit (id)
);

insert into toinherit (id) values (1);
insert into toinherit (id) values (2);
insert into leftside (id, leftname) values (1, 'leftname1');
insert into rightside (id, rightname) values (2, 'rightname2');
insert into linkthem (left_id, right_id) values (1, 2);

...it works:

CREATE TABLE
CREATE TABLE
INSERT 55887 1
INSERT 55888 1
INSERT 55889 1
INSERT 55890 1
INSERT 55891 1

Is this a bug, or a feature? It seems I can't make a column reference
work directly with the table that gets inherited by the others. Neither
can I make a column reference work with a table that *inherits* the
toinherit table. If I can't get this to work, I'll have to revert back
to not using inheritance at all.

thanks,

/s.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2004-02-25 00:23:35 Re: DBD::Pg 1.32 ready for testing
Previous Message Tom Lane 2004-02-24 23:28:36 Re: select statement against pg_stats returns inconsistent data