Re: inherited tables failure

On Thu, 16 Aug 2001 pgsql-bugs(at)postgresql(dot)org wrote:

> Laszlo Csite (ozy(at)tiszanet(dot)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.