Re: DELETE syntax on JOINS

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