Re: Minor improvement to delete.sgml

On Sun, Nov 13, 2016 at 10:55 PM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2016/10/19 2:51, Robert Haas wrote:
>>
>> On Fri, Oct 14, 2016 at 12:05 AM, Etsuro Fujita
>> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>>
>>> I think it's better to mention that an alias is needed for the target
>>> table
>>> specified in the USING clause of a DELETE statement, to set up a
>>> self-join,
>>> as the documentation on the from_list parameter of UPDATE does. Please
>>> find
>>> attached a patch.
>
>> The statement you are proposing to add to the documentation isn't true.
>
> Consider a counterexample of DELETE doing a self-join of a target table:
>
> postgres=# create table t1 (c1 int);
> CREATE TABLE
> postgres=# insert into t1 values (1);
> INSERT 0 1
> postgres=# delete from t1 using t1 where t1.c1 = t1.c1;
> ERROR: table name "t1" specified more than once
>
> Giving an alias to the target table t1 in the USING clause,
>
> postgres=# delete from t1 using t1 r1 where t1.c1 = r1.c1;
> DELETE 1
>
> Am I missing something?

Well, you could also alias the target table, like this:

delete from t1 q1 using t1 where q1.c1 = t1.c1;

The point is that, while it's true that you can't have the same table
alias twice at the same query level, you can fix that in more than one
way. Your suggestion of adding an alias to the appearance in the
using list is one approach, but not the only one.

I don't think there's any real need for a documentation change here.
The fact that repeating a table alias doesn't work is not unique to
DELETE, nor is it unique to self-joins. The documentation here just
needs to explain that repeating the table name will set up a
self-join; it doesn't need to describe every SQL mistake that you
could make while trying to do so.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company