Re: DELETE syntax on JOINS

2009/8/24 Tom Lane <tgl@sss.pgh.pa.us>:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Bruce Momjian wrote:
>>> So the problem is that our DELETE ... USING does not allow ANSI join
>>> syntax?  Can that be added?
>
>> Not sure about that.  USING is already an extension to the standard, so
>> if we extend it a bit more, it can't be a problem, can it?
>
> I don't see any very good way to extend the USING syntax to allow the
> target table to be outer-joined to something else.  Some other systems
> allow it by letting you re-specify the target in the other clause,
> equivalently to
>
> 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.

I'm all in favor of compatibility, but if there is any way to make
this work without massive collateral damage, I am also all in favor of
that.  I am forever writing queries that contain a needless self-join
to work around the impossibility of directly outer-joining against the
target.

...Robert