The following bug has been logged on the website:
Bug reference: 16541
Logged by: Michael McLaughlin
Email address: michael.mclaughlin@cpsi.com
PostgreSQL version: 9.6.16
Operating system: CentOS7
Description:
I discovered while copying data from a PostgreSQL 9.6.16 database to a
PostgreSQL 12.2 database that some of my imports were failing because
timestamps in my data are out of range. As it turns out, somehow we wrote
erroneous future dates into timestamp columns in our PG 9.6.16 database
(i.e. '1666771-01-01 00:00:00') and the 9.6.16 allowed this, but when
attempting to copy the data to the 12.2 database I get the out of range
error. Per the PG documentation, the max value for timestamp is the year
294276 AD and this has been the case since version 8.4, but obviously it is
still being allowed in version 9.6.16.
For a very simple demonstration, in my 9.6.16 database running the command
"select '1666771-01-01 00:00:00'::timestamp;" returns the horribly
futuristic timestamp. Executing the same command in my 12.2 database returns
ERROR: timestamp out of range: "1666771-01-01 00:00:00" as would be
expected for both.
Ultimately I know we need to get the data corrected or removed prior to
upgrading, I am simply trying to find the history of this change and more
importantly on what version it was actually changed/resolved.
