Обсуждение: 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