Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/10/static/sql-select.html
Description:
The SYNOPSIS section of the "SELECT" SQL command contains the line
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
(with a boldface "select"), but it is not clear what is meant by that
"select". Further down the page, in the "UNION clause" section (and also
INTERSECTION or EXCEPT), it is written:
select_statement UNION [ ALL | DISTINCT ] select_statement
which uses boldface "select_statement" instead of boldface "select" as in
the synopsis. This is confusing.
It would be great if the boldface "select" in the synopsis could be better
defined.
Problems with pg_upgrade under Windows
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/pgupgrade.html Description: The postgresql service (since 9.x?) runs under Windows with the integrated NetworkService account and not with a dedicated postgres account anymore (at least by using the EnterpriseDB installer). You can't start a shell as NetworkService (which seems not to be necessary). But the owner and permissions of the data directory should be checked and changed afterwards if necessary. Another problem (not directly of pg_upgrade) is, that pg_upgrade internally uses xcopy.exe to copy the pg_clog/pg_xact directory. Now, if somebody redirects the standard output of pg_upgrade.exe but NOT the standard input, xcopy failes silently and the pg_xact directory (or files within) are missing. This is a problem of xcopy.exe, apparently fixed on newer versions of Windows but existent under Windows 7 x64. After many hours of testing and searching, I finally found the cause of the failure here: http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/ab3c0cc7-83c2-4a86-9188-40588b7d1a52/ I can confirm, that redirecting also the standard input of pg_upgrade.exe solves the problem.
Re: Can we only add values to enums?
2018-03-12 20:28 GMT-03:00 PG Doc comments form : > I need to add a value to an enum, so I found this page, which answered my > question. However, I found it quite confusing that it only documents adding > a value. I would expect it to also document how to remove a value from an > enum? Is this not possible? If not, I consider it a bug. But either way, if > it is not possible, you should explicitly document it on this page. > If it is not document, this means that it is not supported. It is not a bug. It is by design. Read the discussion about this feature at [1]. Removing a enum value requires a table rewrite. I'm not sure it is worth a note. [1] https://www.postgresql.org/message-id/4c1b95e2.6090...@dunslane.net -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
2018-03-15 7:18 GMT-03:00 PG Doc comments form :
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/static/sql-select.html
> Description:
>
> The SYNOPSIS section of the "SELECT" SQL command contains the line
>
> [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
>
> (with a boldface "select"), but it is not clear what is meant by that
> "select". Further down the page, in the "UNION clause" section (and also
> INTERSECTION or EXCEPT), it is written:
>
"select" is defined as a sub-select that can appear in the FROM clause
(see the From Clause section).
> select_statement UNION [ ALL | DISTINCT ] select_statement
>
> which uses boldface "select_statement" instead of boldface "select" as in
> the synopsis. This is confusing.
>
It is a bug in the synopsis. UNION et al cannot contain some elements
(such as ORDER BY) that is allowed for a sub-select. The attached
patch replace "select" with the correct element ("select_statement").
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index b5d3d3a..5acd749 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -40,7 +40,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressiongrouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
-[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
+[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_statement ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
Re: Documentation for varbit is missing size parameter
2018-03-11 1:43 GMT-03:00 PG Doc comments form : > The documentation for the varbit data type is missing the size parameter "[ > (n) ]". > Good catch! It seems to be an oversight in commit 768b647ead78d0d63915c1708cad13c2468f9440. The attached patch adds it. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 9aa9b28..53059d5 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -60,7 +60,7 @@ bit varying [ (n) ] - varbit + varbit [ (n) ] variable-length bit string
Re: "IS NOT DOCUMENT" is missing
2018-03-09 0:10 GMT-03:00 PG Doc comments form : > From testing, it seems like PostgreSQL supports the syntax "IS NOT DOCUMENT" > in addition to "IS DOCUMENT", similar to "IS NOT NULL", "IS NOT FALSE", etc, > however this does not appear to be documented, only "IS DOCUMENT" is. > It has been like that since day 1. I'm not sure why it was not documented. It already has some tests. I'll bet that was an oversight. Should we repeat the statement in another item (like the attached patch)? Another option is to add a statement in the "IS DOCUMENT" item. I'm afraid that NULL return wouldn't be clear. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2f59af2..fcef246 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10399,6 +10399,24 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; + +IS NOT DOCUMENT + + + IS NOT DOCUMENT + + + +xml IS NOT DOCUMENT + + + + The expression IS NOT DOCUMENT returns false if the + argument XML value is a proper XML document, true if it is not (that is, + it is a content fragment), or null if the argument is null. + + + XMLEXISTS
Re: Synopsis of SELECT statement: UNION, INTERSECTION, EXCEPT
Euler Taveira writes:
> 2018-03-15 7:18 GMT-03:00 PG Doc comments form :
>> The SYNOPSIS section of the "SELECT" SQL command contains the line
>> [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
>> (with a boldface "select"), but it is not clear what is meant by that
>> "select".
> It is a bug in the synopsis. UNION et al cannot contain some elements
> (such as ORDER BY) that is allowed for a sub-select. The attached
> patch replace "select" with the correct element ("select_statement").
Well, "select_statement" isn't defined in the synopsis either.
We do define it implicitly in the UNION/INTERSECT/EXCEPT subsections,
but is that enough?
I think the parenthetical remark in the "UNION Clause" section is a bit
shaky too:
(ORDER BY and LIMIT can be attached to a subexpression if it is
enclosed in parentheses. Without parentheses, these clauses will be
taken to apply to the result of the UNION, not to its right-hand input
expression.)
This would seem to imply that
SELECT * FROM foo ORDER BY x UNION SELECT * FROM bar
is legal and means the same as
(SELECT * FROM foo UNION SELECT * FROM bar) ORDER BY x
which is wrong.
Also, whether or not that wording is right, it's not duplicated in the
INTERSECT or EXCEPT headings, where logically it ought to appear too.
We could duplicate it there maybe, but I'm starting to feel like this is
just doubling down on a bad documentation design.
The long and short of it is that UNION et al were wedged into the initial
synopsis in a way that's at best misleading and at worst a lie. But
I'm not sure how to make that better without also making it a lot more
confusing. A pedantically correct syntax synopsis would look something
like
[ WITH [ RECURSIVE ] with_query [, ...] ]
select_stmt [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_stmt ...]
where select_stmt is
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST |
LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [,
...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
and that's still not right because ORDER BY et al can't be attached to a
select_stmt that's the argument of a set operation, so really we'd need
a couple of levels of nonterminals before we get down to the basic
"SELECT expression FROM ..." part. Nor has the use of parentheses been
mentioned yet.
If you look at either the SQL standard's syntax diagrams or our actual
bison grammar, they're both unreasonably complicated. Novices would
not thank us for reproducing that in full detail in the basic SELECT
syntax summary, and I'm not sure experts would either.
So, surely there's room for improvement here, but I'm not certain what
it'd look like. Maybe we should split out the discussion of set-operation
syntax altogether? How exactly?
regards, tom lane
