Re: Section 4.1.2.7 contains false information

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/sql-syntax-lexical.html
Description:

QUOTE:
The CAST() syntax conforms to SQL. The type 'string' syntax is a
generalization of the standard: SQL specifies this syntax only for a few
data types, but PostgreSQL allows it for all types. The syntax with :: is
historical PostgreSQL usage, as is the function-call syntax.
UNQUOTE

In fact, this is not 100% true. TIMESTAMP 'string' does not work this way.
It is mentioned in section 4.2.9 that TIMESTAMP( 'string') cannot be used,
but it does not mention TIMESTAMP 'string'.

I would really prefer if the respective information could be clear,
unequivocal, complete and included in other sections of the manual where it
is due, i.e. in sections 8.5 and 9.9 which deal with date & time types and
operators.

PG Doc comments form <noreply(at)postgresql(dot)org> writes:
> QUOTE:
> The CAST() syntax conforms to SQL. The type 'string' syntax is a
> generalization of the standard: SQL specifies this syntax only for a few
> data types, but PostgreSQL allows it for all types. The syntax with :: is
> historical PostgreSQL usage, as is the function-call syntax.
> UNQUOTE

> In fact, this is not 100% true. TIMESTAMP 'string' does not work this way.

Looks like it works to me:

regression=# select timestamp '2019-04-09 11:49';
timestamp
---------------------
2019-04-09 11:49:00
(1 row)

If you feel that the documentation is unclear, you need to be clearer
about how it's unclear ;-)

(Reading between the lines of this complaint and your adjacent one,
I kind of suspect that you were trying to use "TIMESTAMP something"
where the something wasn't a literal string constant. But surely
4.1.2.7 makes it plain that the discussed syntax is for constants.
You might need to read 4.2.9 "Type Casts" instead.)

regards, tom lane

Dear Tom, it works only in psql, it does not work when you talk to the server using postgresql protocol. That is my point.

e.g. in the following code the query causes Syntax error (see the text in red). That means it does not work. Interestingly, when I use $4::timestamp or CAST ( $4 as TIMESTAMP ) the syntax error does not occur.

// contest.ts

import { Pool } from 'pg' ;

/** Class representing database access */

export class Db {

/** PG connection pool */

pool : Pool ;

constructor() {

this.pool = new Pool( {user: 'contest_owner', database: 'contest'} ); // database name and owner

}

...

/** Create new contest instance record

* @param contestId {string} Identifier of the contest as specified in the contest log format

* @param contestName {string} Human readable contest name for software user

* @param startUtc {string} Start date and time of the contest in UTC 'YYYY-MM-DD hh:mm'

* @param endUtc {string} End date and time of the contest in UTC 'YYYY-MM-DD hh:mm'

* @param format {string} Name of the expected log format (Cabrillo, EDI, ADIF, Garlic)

*/

createContest( contestId: string, periodId: string, contestName : string,

startUtc: string, endUtc: string, format : string ): Promise<number|undefined> {

if( !startUtc.match(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}(\:\d{2})?$/)) {

return Promise.reject('Incorrect format of UTC start date and time, should be "YYYY-MM-DD hh:mm[:ss]"');

}

if( !endUtc.match(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}(\:\d{2})?$/)) {

return Promise.reject('Incorrect format of UTC end date and time, should be "YYYY-MM-DD hh:mm[:ss]"');

}

return this.pool.query(

"INSERT INTO public.contest (contest_id, period_id, contest_name, start_ts, end_ts, default_format, status ) "

+ "VALUES ($1, $2, $3, TIMESTAMP $4, TIMESTAMP $5, $6, 'NEW' ) RETURNING contest_key",

[contestId, periodId, contestName, startUtc, endUtc, format]

).then( result => { if( result.rowCount > 0 ) { return result.rows[0].contest_key } else { return undefined; } });

}

...

}

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, April 9, 2019 5:57 PM
To: jindrich(at)vavruska(dot)cz
Cc: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Re: Section 4.1.2.7 contains false information

PG Doc comments form <noreply(at)postgresql(dot)org<mailto:noreply(at)postgresql(dot)org>> writes:

> QUOTE:

> The CAST() syntax conforms to SQL. The type 'string' syntax is a

> generalization of the standard: SQL specifies this syntax only for a

> few data types, but PostgreSQL allows it for all types. The syntax

> with :: is historical PostgreSQL usage, as is the function-call syntax.

> UNQUOTE

> In fact, this is not 100% true. TIMESTAMP 'string' does not work this way.

Looks like it works to me:

regression=# select timestamp '2019-04-09 11:49';

timestamp

---------------------

2019-04-09 11:49:00

(1 row)

If you feel that the documentation is unclear, you need to be clearer about how it's unclear ;-)

(Reading between the lines of this complaint and your adjacent one, I kind of suspect that you were trying to use "TIMESTAMP something"

where the something wasn't a literal string constant. But surely

4.1.2.7 makes it plain that the discussed syntax is for constants.

You might need to read 4.2.9 "Type Casts" instead.)

regards, tom lane

On Tue, Apr 9, 2019 at 3:03 PM Jind?ich Vavru?ka <jindrich(at)vavruska(dot)cz>
wrote:

> Dear Tom, it works only in psql, it does not work when you talk to the
> server using postgresql protocol. That is my point.
>
> e.g. in the following code the query causes Syntax error (see the text in
> red). That means it does not work. Interestingly, when I use $4::timestamp or
> CAST ( $4 as TIMESTAMP ) the syntax error does not occur.
>
>
>
> "INSERT INTO public.contest (contest_id, period_id, contest_name,
> start_ts, end_ts, default_format, status ) "
>
> + "VALUES ($1, $2, $3, *TIMESTAMP $4*, *TIMESTAMP $5*, $6, 'NEW'
> ) RETURNING contest_key",
>
>
> And further on in that section its plainly states:

"To avoid syntactic ambiguity, the type 'string' syntax can only be used to
specify the type of a simple literal constant."

Since what you've provided is not a simple literal constant it is expected
to not work - and that either :: or cast() needs to be used instead.

If you'd like to make a concrete documentation suggestion please do so.
Given the scarcity of complaints seen here, and the fact that not
everything can and should be documented everywhere, I'm not presently
seeing an issue large enough to change the status quo. Especially since
its sounds more like you didn't read the section the explicitly covers the
limitations of typename 'literal' and the universality of actual casting
syntax (either SQL standard CAST or PostgreSQL double-colon).

There is indeed a lot of material here and sometime a quick question on the
email lists is more efficient a learning mechanism than trying to get the
documentation perfect. This seems like one of those times.

David J.