pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedConfusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
4+ messages / 4 participants
[nested] [flat]
* Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
@ 2019-04-09 14:50 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2019-04-09 14:50 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: jindrich@vavruska.cz
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/datatype-datetime.html
Description:
Both sections mention syntax TIMESTAMP '2019-01-20 08:00' as valid for input
of timestamp data. This seems to work fine in psql, however, this syntax
causes Syntax error when used in library access to the server (in my case it
is via pg module in NodeJS). After spending half a day looking for error, I
learned from the javascript module developer that the mentioned syntax
cannot be used for programmatic access. Instead, he recommended to use cast
operator form '2019-01-20 08:00'::timestamp.
I tested his suggestion (it works, of course) and was looking for some
mention or confirmation in the documentation, but found nothing. I think
this is a serious issue, especially because the alternative possibility of
using ::timestamp is not even mentioned in chapters 8.5 or 9.9. If someone
(like me) looks for specific information how to handle date & time literals,
they will inevitably fall into the same trap.
Since the experienced Syntax error is contrary to what one would expect
after reading the SQL language manual, could you please at least add some
hyperlink in both sections 8.5 and 9.9 to attract reader's attention to this
specific behavior of the database server? Thank you.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
@ 2019-04-13 02:49 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2019-04-13 02:49 UTC (permalink / raw)
To: jindrich@vavruska.cz; pgsql-docs@lists.postgresql.org
On Tue, Apr 9, 2019 at 02:50:14PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/11/datatype-datetime.html
> Description:
>
> Both sections mention syntax TIMESTAMP '2019-01-20 08:00' as valid for input
> of timestamp data. This seems to work fine in psql, however, this syntax
> causes Syntax error when used in library access to the server (in my case it
> is via pg module in NodeJS). After spending half a day looking for error, I
> learned from the javascript module developer that the mentioned syntax
> cannot be used for programmatic access. Instead, he recommended to use cast
> operator form '2019-01-20 08:00'::timestamp.
> I tested his suggestion (it works, of course) and was looking for some
> mention or confirmation in the documentation, but found nothing. I think
> this is a serious issue, especially because the alternative possibility of
> using ::timestamp is not even mentioned in chapters 8.5 or 9.9. If someone
> (like me) looks for specific information how to handle date & time literals,
> they will inevitably fall into the same trap.
> Since the experienced Syntax error is contrary to what one would expect
> after reading the SQL language manual, could you please at least add some
> hyperlink in both sections 8.5 and 9.9 to attract reader's attention to this
> specific behavior of the database server? Thank you.
We don't get this question very often. I wonder if you didn't look at
the error message we generated, or if you could share the exact error
you saw.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
@ 2019-04-13 07:30 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2019-04-13 07:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: jindrich@vavruska.cz <jindrich@vavruska.cz>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Friday, April 12, 2019, Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Apr 9, 2019 at 02:50:14PM +0000, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> I think
> > this is a serious issue, especially because the alternative possibility
> of
> > using ::timestamp is not even mentioned in chapters 8.5 or 9.9. If
> someone
> > (like me) looks for specific information how to handle date & time
> literals,
> > they will inevitably fall into the same trap.
> > Since the experienced Syntax error is contrary to what one would expect
> > after reading the SQL language manual, could you please at least add some
> > hyperlink in both sections 8.5 and 9.9 to attract reader's attention to
> this
> > specific behavior of the database server? Thank you.
>
> We don't get this question very often. I wonder if you didn't look at
> the error message we generated, or if you could share the exact error
> you saw.
>
>
See also:
https://www.postgresql.org/message-id/flat/CAKFQuwYXhvhr8hWd1ZjWedediHiC2pY4SrvOwSNDVu8sguBhdQ%40mai...
The docs maybe aren’t great at covering this but they do, and correctly.
Though maybe a note saying “this works but you probably should use actual
casts” would be warranted. I personally have not found a need to use the
“type” ‘literal’ syntax.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
@ 2019-04-15 04:16 Jindřich Vavruška <jindrich@vavruska.cz>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jindřich Vavruška @ 2019-04-15 04:16 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Hello,
I thought about it once more.
I think I fell into trap of my own experience. I do not work with PG very often, that’s why I went into the manual to make sure what is the actual syntax. Second, in most cases in my professional use of any rdbms, we work with scripts (or PL/SQL procedures) and never use database API (my specialization are data warehouses). That’s probably why I never really cared if there were any differences in SQL between script execution and API call.
This was a use case that went out of my usual experience and that’s why I made a mistake that very few active developers or users of PostgreSQL can understand 😊
You guys decide if this single case requires any fixes in docs. If this has never happened in the last ten years, probably not.
Regards,
Jindra
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Saturday, April 13, 2019 9:31 AM
To: Bruce Momjian <bruce@momjian.us>
Cc: jindrich@vavruska.cz; pgsql-docs@lists.postgresql.org
Subject: Re: Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
On Friday, April 12, 2019, Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>> wrote:
On Tue, Apr 9, 2019 at 02:50:14PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
I think
> this is a serious issue, especially because the alternative possibility of
> using ::timestamp is not even mentioned in chapters 8.5 or 9.9. If someone
> (like me) looks for specific information how to handle date & time literals,
> they will inevitably fall into the same trap.
> Since the experienced Syntax error is contrary to what one would expect
> after reading the SQL language manual, could you please at least add some
> hyperlink in both sections 8.5 and 9.9 to attract reader's attention to this
> specific behavior of the database server? Thank you.
We don't get this question very often. I wonder if you didn't look at
the error message we generated, or if you could share the exact error
you saw.
See also:
https://www.postgresql.org/message-id/flat/CAKFQuwYXhvhr8hWd1ZjWedediHiC2pY4SrvOwSNDVu8sguBhdQ%40mai...;
The docs maybe aren’t great at covering this but they do, and correctly. Though maybe a note saying “this works but you probably should use actual casts” would be warranted. I personally have not found a need to use the “type” ‘literal’ syntax.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-04-15 04:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-09 14:50 Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators) PG Doc comments form <noreply@postgresql.org>
2019-04-13 02:49 ` Bruce Momjian <bruce@momjian.us>
2019-04-13 07:30 ` David G. Johnston <david.g.johnston@gmail.com>
2019-04-15 04:16 ` Jindřich Vavruška <jindrich@vavruska.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox