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

On 09.11.2015 17:41, Tom Lane wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.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