Re: DELETE syntax on JOINS

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: DELETE syntax on JOINS
Date: 2009-08-25 13:24:14
Message-ID: 603c8f070908250624q7217aee0vba3c669b3ae193ae@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
> All,
>
>>> DELETE FROM target t USING t LEFT JOIN other_table ot ON ...
>>>
>>> but we have always considered that the target is *not* to be identified
>>> with any member of the FROM/USING clause, so it would be a serious
>>> compatibility break to change that now.
>
> What I don't get is why this is such a usability issue.  Subqueries in
> DELETE FROM work perfectly well, and provide more flexibility than most
> users know what to do with.
>
> Personally, I'd be happy just to stop with the SQL extension we have.  I
> think extending USING any further is going to cause more problems than
> it solves.

It's both a usability issue and a performance issue. Suppose you want
to select all the rows in foo whose id field does not appear in
bar.foo_id. The most efficient way to do this in PostgreSQL is
typically:

SELECT foo.* FROM foo LEFT JOIN bar ON foo.id = bar.foo_id WHERE
bar.foo_id IS NULL;

Now, if you want to delete those rows, you can't do it without an
extra join somewhere. You can do it like this:

DELETE FROM foo AS foo1
USING foo AS foo2 LEFT JOIN bar ON foo2.id = bar.foo_id
WHERE foo1.id = foo2.id AND foo2;

Or like this:

DELETE FROM foo WHERE id IN (SELECT foo.id FROM foo LEFT JOIN bar ON
foo.id = bar.foo_id WHERE bar.foo_id IS NULL);

...but either way you now have foo in there twice when it really
shouldn't need to be, and you're doing a useless self-join to work
around a syntax limitation.

[ thinks ]

Actually, I guess in this case you can get around it like this:

DELETE FROM foo WHERE NOT EXISTS (SELECT 1 FROM bar WHERE bar.foo_id = foo.id);

...but I'm not sure it can be rewritten that way in every case - in
particular, that won't work if you have a RETURNING clause that
includes a value taken from bar.

...Robert

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-08-25 13:33:15 Re: setting up scan keys
Previous Message Bruce Momjian 2009-08-25 13:02:02 Re: DELETE syntax on JOINS