Thread: Question Regarding DELETE FROM ONLY

Hello List:

Please observe the following example that reproduces my problem:

CREATE TABLE ptable (code VARCHAR) WITHOUT OIDS;
CREATE TABLE

CREATE TABLE ctable (code VARCHAR, name VARCHAR)
    INHERITS (ptable) WITHOUT OIDS;
NOTICE:  merging column "code" with inherited definition
CREATE TABLE

INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
rnd=# SELECT * FROM ptable;
   code
----------
 code_one
(1 row)

rnd=# SELECT * FROM ctable;
        code   |   name
---------------+----------
 code_one | Code One
(1 row)

DELETE FROM ONLY ctable WHERE code ~* 'code_one';

rnd=# SELECT * FROM ptable;
 code
------
(0 rows)

The record in ctable AND the record in ptable are both deleted even though I
specified "ONLY ctable" in the delete phrase. Why is this happening? The two
tables in this example represent a greatly simplified version of what I'm
doing in developing an application and if "ONLY" doesn't work then I've got a
big problem. Have I misunderstood inheritance altogether?

TIA...

rnd=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)

On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote:
> INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
> rnd=# SELECT * FROM ptable;
>    code
> ----------
>  code_one
> (1 row)
>
> rnd=# SELECT * FROM ctable;
>         code   |   name
> ---------------+----------
>  code_one | Code One
> (1 row)

These aren't two distinct records -- they're the same record, the
one in ctable, as the following queries show:

SELECT tableoid::regclass, * FROM ptable;
SELECT tableoid::regclass, * FROM ctable;

You can use FROM ONLY to see that the record doesn't actually exist
in ptable:

SELECT tableoid::regclass, * FROM ONLY ptable;

> DELETE FROM ONLY ctable WHERE code ~* 'code_one';
>
> rnd=# SELECT * FROM ptable;
>  code
> ------
> (0 rows)
>
> The record in ctable AND the record in ptable are both deleted even though I
> specified "ONLY ctable" in the delete phrase. Why is this happening?

Because there was only one record, the one in ctable, and you deleted
it.  When you inserted the record into ctable that's the only place
it went.  The query against ptable showed records in the parent
table (none) and records in its child tables (one).  After you
delete the record from the child the subsequent query against the
parent returns zero rows because both tables are now empty (the
parent was always empty and the child had its one record deleted).

--
Michael Fuhr

On Monday 29 May 2006 09:43 am, Michael Fuhr <mike@fuhr.org> thus
communicated:
--> On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote:
--> > INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
--> > rnd=# SELECT * FROM ptable;
--> >    code
--> > ----------
--> >  code_one
--> > (1 row)
--> >
--> > rnd=# SELECT * FROM ctable;
--> >         code   |   name
--> > ---------------+----------
--> >  code_one | Code One
--> > (1 row)
-->
--> These aren't two distinct records -- they're the same record, the
--> one in ctable, as the following queries show:
-->
--> SELECT tableoid::regclass, * FROM ptable;
--> SELECT tableoid::regclass, * FROM ctable;
-->
--> You can use FROM ONLY to see that the record doesn't actually exist
--> in ptable:
-->
--> SELECT tableoid::regclass, * FROM ONLY ptable;
-->
--> > DELETE FROM ONLY ctable WHERE code ~* 'code_one';
--> >
--> > rnd=# SELECT * FROM ptable;
--> >  code
--> > ------
--> > (0 rows)
--> >
--> > The record in ctable AND the record in ptable are both deleted even
 though I --> > specified "ONLY ctable" in the delete phrase. Why is this
 happening? -->
--> Because there was only one record, the one in ctable, and you deleted
--> it.  When you inserted the record into ctable that's the only place
--> it went.  The query against ptable showed records in the parent
--> table (none) and records in its child tables (one).  After you
--> delete the record from the child the subsequent query against the
--> parent returns zero rows because both tables are now empty (the
--> parent was always empty and the child had its one record deleted).
-->
--> --
--> Michael Fuhr
-->

Thanks for the response Michael. I'm beginning to see the light.