inherited tables failure

On Thu, 16 Aug 2001 pgsql-bugs@postgresql.org wrote:

Laszlo Csite (ozy@tiszanet.hu) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
inherited tables failure

Long Description

We have two tables inherited one from the other one. If you try to
insert from the parent into the child by an "INSERT INTO" statement
then the record is inserted into the child but into the parent too!
Therefore in the parent duplicated rows appear.

The other bug is if you delete a row from the parent then it erases
from the child too. see the illustration below.

create table try (col1 int4);
create table try1 () inherits (try);
insert into try (col1) values (15);
select * from try1; --> you get 0 row
select * from try; --->you get 1 row
insert into try1 select * from try; --> the answer is 1 row is inserted into try1

select * from try1; --> you get 1 row
select * from try; --->you get 2 row !!!!!!!!!!! <-That's wrong

No, that's the intended behavior. The default behavior as of 7.1 (I
believe) is that sql queries occur across inheritance trees. So the latter
is select from try and any subtables. If you only want try, use ONLY
(select * from ONLY try). There's only one copy of the row, however.

delete from try;
select * from try1; --> you get 0 row !!!!!!!!!!! <-That's wrong
select * from try; --->you get 0 row

No, that's also intended. As above, delete from try means delete from
try and any subtables and only means delete from only that table.

pgsql-bugs@postgresql.org writes:

Long Description
We have two tables inherited one from the other one. If you try to insert from the parent into the child by an "INSERT INTO" statement then the record is inserted into the child but into the parent too! Therefore in the parent duplicated rows appear.
The other bug is if you delete a row from the parent then it erases from the child too. see the illustration below.

This is not a bug; it's the way things are supposed to work in 7.1.
"*" is now the default behavior. Use ONLY if you want to restrict
scans or updates to the parent table.

regards, tom lane