BUG #16541: Timestamp allowing greater than max documented value?
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.
PG Bug reporting form <noreply@postgresql.org> writes:
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.
I believe the actual story is that floating-point timestamps allow a much
wider date range than integer timestamps (with corresponding loss of
precision as you get further away from the epoch date). The default
for integer_datetimes changed to "on" in 8.4, but your 9.6 installation
must have been built with it turned off.
This is documented, if not too prominently. If you check
https://www.postgresql.org/docs/9.6/datatype-datetime.html
the second "Note" includes
Note that using floating-point datetimes allows a larger range of
timestamp values to be represented than shown above: from 4713 BC
up to 5874897 AD.
That note is gone in more recent branches because we removed the
floating-point timestamp support altogether.
regards, tom lane