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

>Chris Bartlett <c(dot)bartlett(at)paradise(dot)net(dot)nz> writes:
>> I'm trying to set [now(), 2049-12-31 00:00:00) as the default for a
>> tsrange column (Postgres 9.2), but can't figure out how to do it. I'm
>> either getting syntax errors or now() is being evaluated, so that the
>> default becomes something like [2012-07-14 14:04:35, 2049-12-31
>> 00:00:00), which is not what I want. Can anyone point me in the right
>> direction, please?
>
>I think you'd need to use the constructor function, ie
>
> default tsrange(now(), '2049-12-31 00:00:00')

I had tried the constructor function and hadn't managed to get a
successful result. E.g.

alter table the_table alter column the_column set default
tsrange(now(), '2049-12-31 00:00:00');
-> ERROR: function tsrange(timestamp with time zone, unknown) does not exist
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

Realisation: now() is a timestamp with time zone, but my column is a
timestamp without time zone. So this works:

alter table the_table alter column the_column set default
tsrange(now()::timestamp without time zone, '2049-12-31
00:00:00'::timestamp without time zone);
-> ALTER TABLE

>BTW, that second value looks a whole lot like a poorly thought out
>substitute for 'infinity' ...
> regards, tom lane

That's certainly an interesting comment and I'm open to suggestions!
The original db has two columns (from_timestamp, to_timestamp). I
don't go for NULL in the to_timestamp column. Alternatively, a
timestamp very, very far in the future can throw off query planners.

Thanks,
Chris