On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkuswrote:
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
