Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Wed, Mar 18, 2020 at 12:24:45PM -0400, Bruce Momjian wrote: > On Tue, Mar 17, 2020 at 10:58:54PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > I have implemented the ideas above in the attached patch. I have > > > synchronized the syntax to match SELECT, and synchronized the paragraphs > > > describing the item. > > > > I think that the DELETE synopsis should look like > > > > [ USING from_item [, ...] ] > > > > so that there's not any question which part of the SELECT syntax we're > > talking about. I also think that the running text in both cases should > > say in exactly these words "from_item means the same thing as it does > > in SELECT"; the wording you propose still seems to be dancing around > > the point, leaving readers perhaps not quite sure about what is meant. > > > > In the DELETE case you could alternatively say "using_item means the same > > thing as from_item does in SELECT", but that doesn't really seem like an > > improvement to me. > > OK, updated patch attached. Patch appied through 9.5. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Wednesday, March 18, 2020, Tom Lane wrote: > Bruce Momjian writes: > > OK, updated patch attached. > > LGTM, thanks > +1 David J.
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
Bruce Momjian writes: > OK, updated patch attached. LGTM, thanks. regards, tom lane
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Tue, Mar 17, 2020 at 10:58:54PM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > I have implemented the ideas above in the attached patch. I have
> > synchronized the syntax to match SELECT, and synchronized the paragraphs
> > describing the item.
>
> I think that the DELETE synopsis should look like
>
> [ USING from_item [, ...] ]
>
> so that there's not any question which part of the SELECT syntax we're
> talking about. I also think that the running text in both cases should
> say in exactly these words "from_item means the same thing as it does
> in SELECT"; the wording you propose still seems to be dancing around
> the point, leaving readers perhaps not quite sure about what is meant.
>
> In the DELETE case you could alternatively say "using_item means the same
> thing as from_item does in SELECT", but that doesn't really seem like an
> improvement to me.
OK, updated patch attached.
--
Bruce Momjian https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index df8cea48cf..08fb032b50 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -23,7 +23,7 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
-[ USING using_list ]
+[ USING from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -117,17 +117,17 @@ DELETE FROM [ ONLY ] table_name [ *
-using_list
+from_item
- A list of table expressions, allowing columns from other tables
- to appear in the WHERE condition. This is similar
- to the list of tables that can be specified in the of a
- SELECT statement; for example, an alias for
- the table name can be specified. Do not repeat the target table
- in the using_list,
- unless you wish to set up a self-join.
+ A table expression allowing columns from other tables to appear
+ in the WHERE condition. This uses the same
+ syntax as the
+ of a SELECT statement; for example, an alias
+ for the table name can be specified. Do not repeat the target
+ table as a from_item
+ unless you wish to set up a self-join (in which case it must appear
+ with an alias in the from_item).
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index f58dcd8877..07958e7447 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -27,7 +27,7 @@ UPDATE [ ONLY ] table_name [ * ] [
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
-[ FROM from_list ]
+[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -164,17 +164,17 @@ UPDATE [ ONLY ] table_name [ * ] [
-from_list
+from_item
- A list of table expressions, allowing columns from other tables
- to appear in the WHERE condition and the update
- expressions. This is similar to the list of tables that can be
- specified in the of a SELECT
- statement. Note that the target table must not appear in the
- from_list, unless you intend a self-join (in which
- case it must appear with an alias in the from_list).
+ A table expression allowing columns from other tables to appear in
+ the WHERE condition and update expressions. This
+ uses the same syntax as the of a SELECT statement;
+ for example, an alias for the table name can be specified. Do not
+ repeat the target table as a from_item
+ unless you intend a self-join (in which case it must appear with
+ an alias in the from_item).
@@ -264,7 +264,7 @@ UPDATE count
When a FROM clause is present, what essentially happens
is that the target table is joined to the tables mentioned in the
- from_list, and each output row of the join
+ from_item list, and each output row of the join
represents an update operation for the target table. When using
FROM you should ensure that the join
produces at most one output row for each row to be modified. In
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
Bruce Momjian writes: > I have implemented the ideas above in the attached patch. I have > synchronized the syntax to match SELECT, and synchronized the paragraphs > describing the item. I think that the DELETE synopsis should look like [ USING from_item [, ...] ] so that there's not any question which part of the SELECT syntax we're talking about. I also think that the running text in both cases should say in exactly these words "from_item means the same thing as it does in SELECT"; the wording you propose still seems to be dancing around the point, leaving readers perhaps not quite sure about what is meant. In the DELETE case you could alternatively say "using_item means the same thing as from_item does in SELECT", but that doesn't really seem like an improvement to me. regards, tom lane
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Thu, Feb 13, 2020 at 11:26:45AM -0500, Tom Lane wrote:
> I see where you're coming from, but I do not think that repeating the
> whole from_item syntax in UPDATE and DELETE is the best way forward.
> In the first place, we'd inevitably forget to update those copies,
> and in the second, I'm not sure that the syntax is all that helpful
> without all the supporting text in the SELECT ref page --- which
> surely we aren't going to duplicate.
>
> I think the real problem with the places Alexey is on about is that
> they're too waffle-y. They use wording like "similar to", leaving
> one wondering what discrepancies exist but are being papered over.
> In point of fact, as a look into gram.y will show, what you can
> write after UPDATE ... FROM or DELETE ... USING is *exactly* the
> same thing as what you can write after SELECT ... FROM. So what
> I'm in favor of here is:
>
> * Change the synopsis entries to look like "FROM from_item [, ...]"
> and "USING from_item [, ...]", so that they match the SELECT
> synopsis exactly.
>
> * In the text, describe from_item as being exactly the same as
> it is in SELECT.
>
> (Compare the handling of with_query, which has pretty much the
> same problem of being way too complex to document three times.)
I have implemented the ideas above in the attached patch. I have
synchronized the syntax to match SELECT, and synchronized the paragraphs
describing the item.
--
Bruce Momjian https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index df8cea48cf..9f0ef0e681 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -23,7 +23,7 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
-[ USING using_list ]
+[ USING using_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -117,17 +117,18 @@ DELETE FROM [ ONLY ] table_name [ *
-using_list
+using_item
- A list of table expressions, allowing columns from other tables
- to appear in the WHERE condition. This is similar
- to the list of tables that can be specified in the of a
- SELECT statement; for example, an alias for
- the table name can be specified. Do not repeat the target table
- in the using_list,
- unless you wish to set up a self-join.
+ A table expression allowing columns from other tables to appear
+ in the WHERE condition. This is the same as
+ the table that can be specified in the of a SELECT
+ statement; for example, an alias for the table name can be
+ specified. Do not repeat the target table as a using_item unless you wish to set
+ up a self-join (in which case it must appear with an alias in the
+ using_item).
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index f58dcd8877..d1e74a7c3b 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -27,7 +27,7 @@ UPDATE [ ONLY ] table_name [ * ] [
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
-[ FROM from_list ]
+[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -164,17 +164,18 @@ UPDATE [ ONLY ] table_name [ * ] [
-from_list
+from_item
- A list of table expressions, allowing columns from other tables
- to appear in the WHERE condition and the update
- expressions. This is similar to the list of tables that can be
- specified in the of a SELECT
- statement. Note that the target table must not appear in the
- from_list, unless you intend a self-join (in which
- case it must appear with an alias in the from_list).
+ A table expression allowing columns from other tables to
+ appear in the WHERE condition and update
+ expressions. This is the same as the table that can be specified
+ in the of a
+ SELECT statement; for example, an alias for
+ the table name can be specified. Do not repeat the target table
+ as a from_item unless you intend a
+ self-join (in which case it must appear with an alias in the
+ from_item).
@@ -264,7 +265,7 @@ UPDATE count
When a FROM clause is present, what essentially happens
is that the target table is joined to the tables mentioned in the
- from_list, and each output row of the join
+ from_item list, and each output row of the join
represents an update operation f
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Thu, Feb 13, 2020 at 9:26 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov > wrote: > >> Please could someone have a look at the patch attached? > >> It's not just pedantry but rather based on a real-life example of > >> someone reading and being not sure > >> whether e.g. joins can be used in there. > > > Drive-by comment - I'm on board with the idea but I do not believe this > > patch accomplishes the goal. > > IMO there is too much indirection happening and trying to get terms > exactly > > right, so the user can find or remember them from elsewhere in the > > documentation, doesn't seem like the best solution. The material isn't > > that extensive and since it is covered elsewhere a little bit more > > explicitness in the DELETE and FROM documentation seems like a better > path > > forward. > > I see where you're coming from, but I do not think that repeating the > whole from_item syntax in UPDATE and DELETE is the best way forward. > In the first place, we'd inevitably forget to update those copies, > and in the second, I'm not sure that the syntax is all that helpful > without all the supporting text in the SELECT ref page --- which > surely we aren't going to duplicate. > > I think the real problem with the places Alexey is on about is that > they're too waffle-y. They use wording like "similar to", leaving > one wondering what discrepancies exist but are being papered over. > In point of fact, as a look into gram.y will show, what you can > write after UPDATE ... FROM or DELETE ... USING is *exactly* the > same thing as what you can write after SELECT ... FROM. So what > I'm in favor of here is: > > * Change the synopsis entries to look like "FROM from_item [, ...]" > and "USING from_item [, ...]", so that they match the SELECT > synopsis exactly. > > * In the text, describe from_item as being exactly the same as > it is in SELECT. > > +1 I didn't want a wholesale repetition but the whole "similar to" piece is indeed my issue and this addresses it sufficiently. David J.
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
"David G. Johnston" writes: > On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov wrote: >> Please could someone have a look at the patch attached? >> It's not just pedantry but rather based on a real-life example of >> someone reading and being not sure >> whether e.g. joins can be used in there. > Drive-by comment - I'm on board with the idea but I do not believe this > patch accomplishes the goal. > IMO there is too much indirection happening and trying to get terms exactly > right, so the user can find or remember them from elsewhere in the > documentation, doesn't seem like the best solution. The material isn't > that extensive and since it is covered elsewhere a little bit more > explicitness in the DELETE and FROM documentation seems like a better path > forward. I see where you're coming from, but I do not think that repeating the whole from_item syntax in UPDATE and DELETE is the best way forward. In the first place, we'd inevitably forget to update those copies, and in the second, I'm not sure that the syntax is all that helpful without all the supporting text in the SELECT ref page --- which surely we aren't going to duplicate. I think the real problem with the places Alexey is on about is that they're too waffle-y. They use wording like "similar to", leaving one wondering what discrepancies exist but are being papered over. In point of fact, as a look into gram.y will show, what you can write after UPDATE ... FROM or DELETE ... USING is *exactly* the same thing as what you can write after SELECT ... FROM. So what I'm in favor of here is: * Change the synopsis entries to look like "FROM from_item [, ...]" and "USING from_item [, ...]", so that they match the SELECT synopsis exactly. * In the text, describe from_item as being exactly the same as it is in SELECT. (Compare the handling of with_query, which has pretty much the same problem of being way too complex to document three times.) regards, tom lane
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Thu, Feb 13, 2020 at 11:13:32AM +, Alexey Bashtanov wrote: > Hello, > > Currently the documentation says that one can put "a list of table > expressions" > after FROM in UPDATE or after USING in DELETE. > However, "table expression" is defined as a complex of > FROM, WHERE, GROUP BY and HAVING clauses [1]. > The thing one can list in the FROM clause in a comma-separated manner > is called a table reference [2]. > SELECT reference does not use this term but explains what they could be [3]. > > Please could someone have a look at the patch attached? > It's not just pedantry but rather based on a real-life example of someone > reading and being not sure > whether e.g. joins can be used in there. Thanks for doing this! Speaking of examples, there should be more of them illustrating some of the cases you name. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
On Thu, Feb 13, 2020 at 4:13 AM Alexey Bashtanov wrote: > Hello, > > Currently the documentation says that one can put "a list of table > expressions" > after FROM in UPDATE or after USING in DELETE. > However, "table expression" is defined as a complex of > FROM, WHERE, GROUP BY and HAVING clauses [1]. > The thing one can list in the FROM clause in a comma-separated manner > is called a table reference [2]. > SELECT reference does not use this term but explains what they could be > [3]. > > Please could someone have a look at the patch attached? > It's not just pedantry but rather based on a real-life example of > someone reading and being not sure > whether e.g. joins can be used in there. > > Best, Alex > > [1] https://www.postgresql.org/docs/devel/queries-table-expressions.html > [2] > > https://www.postgresql.org/docs/devel/queries-table-expressions.html#QUERIES-FROM > [3] https://www.postgresql.org/docs/devel/sql-select.html#SQL-FROM Drive-by comment - I'm on board with the idea but I do not believe this patch accomplishes the goal. IMO there is too much indirection happening and trying to get terms exactly right, so the user can find or remember them from elsewhere in the documentation, doesn't seem like the best solution. The material isn't that extensive and since it is covered elsewhere a little bit more explicitness in the DELETE and FROM documentation seems like a better path forward. David J.
Small docs bugfix: make it clear what can be used in UPDATE FROM and DELETE USING
Hello, Currently the documentation says that one can put "a list of table expressions" after FROM in UPDATE or after USING in DELETE. However, "table expression" is defined as a complex of FROM, WHERE, GROUP BY and HAVING clauses [1]. The thing one can list in the FROM clause in a comma-separated manner is called a table reference [2]. SELECT reference does not use this term but explains what they could be [3]. Please could someone have a look at the patch attached? It's not just pedantry but rather based on a real-life example of someone reading and being not sure whether e.g. joins can be used in there. Best, Alex [1] https://www.postgresql.org/docs/devel/queries-table-expressions.html [2] https://www.postgresql.org/docs/devel/queries-table-expressions.html#QUERIES-FROM [3] https://www.postgresql.org/docs/devel/sql-select.html#SQL-FROM diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index df8cea48cf..3f9e067b47 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -120,9 +120,9 @@ DELETE FROM [ ONLY ] table_name [ * using_list - A list of table expressions, allowing columns from other tables + A list of table references, allowing columns from other tables to appear in the WHERE condition. This is similar - to the list of tables that can be specified in the of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index f58dcd8877..44815cc654 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -167,10 +167,10 @@ UPDATE [ ONLY ] table_name [ * ] [ from_list - A list of table expressions, allowing columns from other tables + A list of table references, allowing columns from other tables to appear in the WHERE condition and the update - expressions. This is similar to the list of tables that can be - specified in the of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which
