On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkus<josh@agliodbs.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
