BUG #17390: Function, to_date() -- unexpected values and a request

On Monday, January 31, 2022, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17390
> Logged by: Murthy Kambhampaty
> Email address: smk_va(at)yahoo(dot)com
> PostgreSQL version: 10.7
> Operating system: Windows
> Description:
>
> The to_date() function seems not to fully test out-of-range values. An
> example query and output are listed below:
>
> postgres=> select to_date('1999-00-01', 'YYYY"-"MM"-"DD')
> , to_date('1999.M00', 'YYYY".M"MM')
> postgres-> ;
> to_date | to_date
> ------------+------------
> 1999-01-01 | 1999-01-01
> (1 row)
> (also tested under with 13 and 14)
>
> On the other hand, a too-high value for the month, such as 25, does result
> in the expected out-of-range error. The above seems a bug.
>
>
IIRC, you are correct but we choose not to break existing code in this case.

However,
>
certain input format specifiers are ignored, and thus the result may be
> unexpected.

I would suggest we update the table to note which fields are to be used in
to_char() only.

> I submit, however, that returning '1999-01-01' in response to select
> to_date('1999:Q3', 'YYYY":Q"q') is buggy

>
Well, we did say we ignore “quarter” so we have to output a valid date
given only a year, so 1/1 of that year it is,

In my experience, monthly, quarterly, and weekly dates appear in government
> statistics. (I have deployed PostgreSQL as part of a data-analysis stack
> since version 7.2 or so, because I find it superior to many alternatives
> because PostgreSQL returns errors when fed ambiguous or out-of-range data
> in
> an ETL pipeline, rather than storing unexpected values; the above
> inconsistencies in to_date() processing came as a mild shock.)
>

Pretend to_date doesn’t exist and just write a function that checks for
valid inputs via RegEx and then parse it. Maybe some day someone will
develop a new conversion function that has considerably stricter, and
self-defined, behavior (but given that to_date is basically “close enough
to get the job done” I’m not optimistic). Until then, protect yourself.

David J.