Minor improvement to delete.sgml

Started by Etsuro Fujitaover 9 years ago4 messages

fujita.etsuro@lab.ntt.co.jp

over 9 years ago

1 attachment(s)

Hi,

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.

Best regards,
Etsuro Fujita

Attachments:

doc-delete-sgml.patchbinary/octet-stream; name=doc-delete-sgml.patchDownload

diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 74ea907..0236a52 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -128,7 +128,8 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ *
       <command>SELECT</command> statement; for example, an alias for
       the table name can be specified.  Do not repeat the target table
       in the <replaceable class="PARAMETER">using_list</replaceable>,
-      unless you wish to set up a self-join.
+      unless you wish to set up a self-join (in which case it must appear
+      with an alias in the <replaceable class="PARAMETER">using_list</replaceable>).
      </para>
     </listitem>
    </varlistentry>

Robert Haas

robertmhaas@gmail.com

about 9 years ago

In reply to: Etsuro Fujita (#1)

Re: Minor improvement to delete.sgml

On Fri, Oct 14, 2016 at 12:05 AM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.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.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Etsuro Fujita

fujita.etsuro@lab.ntt.co.jp

about 9 years ago

In reply to: Robert Haas (#2)

Re: Minor improvement to delete.sgml

On 2016/10/19 2:51, Robert Haas wrote:

On Fri, Oct 14, 2016 at 12:05 AM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.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?

Sorry for the delay.

Best regards,
Etsuro Fujita

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas

robertmhaas@gmail.com

about 9 years ago

In reply to: Etsuro Fujita (#3)

Re: Minor improvement to delete.sgml

On Sun, Nov 13, 2016 at 10:55 PM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp> wrote:

On 2016/10/19 2:51, Robert Haas wrote:

On Fri, Oct 14, 2016 at 12:05 AM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers