On Wed, 18 Oct 2023 at 09:16, Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> BTW when adding the tests with extreme values, I noticed this:
>
> test=# select '5874897-01-01'::date;
> date
> ---------------
> 5874897-01-01
> (1 row)
>
> test=# select '5874897-01-01'::date + '1 second'::interval;
> ERROR: date out of range for timestamp
>
That's correct because date + interval returns timestamp, and the
value is out of range for a timestamp. This is equivalent to:
select '5874897-01-01'::date::timestamp + '1 second'::interval;
ERROR: date out of range for timestamp
and I think it's good that it gives a different error from this:
select '294276-01-01'::date::timestamp + '1 year'::interval;
ERROR: timestamp out of range
so you can tell that the overflow in the first case happens before the addition.
Of course a side effect of internally casting first is that you can't
do things like this:
select '5874897-01-01'::date - '5872897 years'::interval;
ERROR: date out of range for timestamp
which arguably ought to return '2000-01-01 00:00:00'. In practice
though, I think it would be far more trouble than it's worth trying to
change that.
Regards,
Dean