Re: 'infinity'::Interval should be added

On Mon, 17 Dec 2018 at 03:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The positive argument for adding infinity to interval is that
> we define operations such as timestamp minus timestamp as
> yielding interval. That's why this has to fail right now:
>
> regression=# select timestamp 'infinity' - timestamp 'now';
> ERROR: cannot subtract infinite timestamps
>

I would like to represent 'infinity' as interval->months = INT_MAX

The documented maximum for an Interval datatype is 178000000 years, which
is 2136000000 months.
but it is possible to have a higher value (up to 2147483647), since we
don't check inputs.
As a result, it is possible that someone is already storing values above
the stated limits, so this would change behavior for them. But if they were
the net effect of it would be the same, it is still a very, very long
interval. It's not long enough to store useful time intervals for geology
or astrophysics, so I doubt it is used at all for that purpose.

Would there be objection to using the INT_MAX value? If so, what else can
be used?

> Of course, there are still cases like timestamp 'infinity' -
> timestamp 'infinity' that would need to fail, but that has a
> semantic basis rather than "the output type can't represent it".
> (No, I don't want to invent an interval equivalent of NaN
> to make that not fail.)
>

Currently

postgres=# select 'infinity'::timestamp = 'infinity'::timestamp;
?column?
----------
t

so I was thinking that

postgres=# select 'infinity'::timestamp - 'infinity'::timestamp;

would be zero rather than an error, for least surprise.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services