| 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: | Whole Thread | Raw Message | 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.
| 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 |