Re: Can't figure out how to use now() in default for tsrange column (PG 9.2)

On 16 Jul 2012, at 17:57, Daniele Varrazzo wrote:

> On Mon, Jul 16, 2012 at 3:56 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
>> On 07/16/2012 07:41 PM, Alban Hertroys wrote:
>>>>>
>>>>> BTW, that second value looks a whole lot like a poorly thought out
>
>> Yup. The 'infinity' value doesn't play well with all database access APIs
>> and languages, though.
>
> It doesn't even play well with PostgreSQL's extract(). I reported it
> some times ago but as of 9.1.4 it has not been fixed.
>
> =# select extract(epoch from 'infinity'::timestamp);
> date_part
> -----------
> 0
>
> This makes 'infinity' a problematic choice in any application
> requiring a mapping between dates and reals, such as when using
> intervals in gist indexes.

Well yeah, obviously. I don't think many mathematicians have wrapped their brain around questions like what day of the week infinity is and whether it's a working day or not. Perhaps Douglas Adams did, in which case it was probably a Tuesday.
I'm just saying, most of the date-parts that extract can retrieve from a timestamp are meaningless with infinity. But, they are also be meaningless with a placeholder date like 31-12-2999.

That said, if it were up to me to decide what the proper epoch value would be for infinity, I'd say NULL - it is unknown as computers simply can't count far enough. It's probably only a matter of time until someone thinks of a solution for that though.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.