Re: Question Regarding DELETE FROM ONLY

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