On Monday, January 31, 2022, PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17390
Logged by: Murthy Kambhampaty
Email address: smk_va@yahoo.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.