Re: BUG #16541: Timestamp allowing greater than max documented value?

PG Bug reporting form <noreply(at)postgresql(dot)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