On Mon, 17 Dec 2018 at 03:48, Tom Lane <t...@sss.pgh.pa.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
