Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 09.11.2015 17:41, Tom Lane wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>> On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> That doesn't seem like enough consensus to commit this patch, which
>>> would change everything to +/-infinity. That particular choice
>>> wouldn't bother me much, but it sounds like other people aren't sold.
>>> I think we need to try to hash that out a little more rather than
>>> rushing into a backward-incompatible change.
>
>> I agree that none of this should be back-patched.
>
> Definitely.
>
>> I agree that a timestamp[tz] of infinity should yield infinity for
>> epoch.
>
> I think everybody is sold on that much.
>
>> My first choice for other things would be NaN, but throwing an
>> error instead would be OK.
>
> Since the function hasn't thrown error for such cases in the past, making
> it do so now would likely break applications. More than once, we've
> had to modify functions to avoid throwing errors so that you don't get
> incidental errors when blindly applying a function to all entries in a
> column. I think going in the opposite direction would elicit protests.

An error will also break legit SQL statements.

> I could see using NaN except for one thing: it'd mean injecting a rather
> fundamental dependence on IEEE math into a basic function definition. You
> can be just about 100% certain that if the SQL committee ever addresses
> this case, it won't be with NaN.

ACK.

> What about returning NULL for the ill-defined cases? That seems to
> comport with SQL's notion of NULL as "unknown/undefined".

This is the first i would expect in such a case.

+ 1 for NULL.

Greetings,
Torsten