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

>Chris Bartlett <c.bartlett@paradise.net.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

Chris Bartlett <c.bartlett@paradise.net.nz> wrote:

> my column is a timestamp without time zone.

In spite of the fact that this was sort of tangential to you primary
question, I'm a little surprised that nobody responded to this
point.  There are very, very few situations where the semantics of
TIMESTAMP WITHOUT TIME ZONE are really what people want; you should
seriously consider using TIMESTAMP WITH TIME ZONE.  In PostgreSQL
neither one actually stores a time zone; the difference is that WITH
TIME ZONE it represents a particular moment in a continuous stream
of time, while WITHOUT TIME ZONE the value can be interpreted to be
any of various times depending on the time zone of the reader.
WITHOUT TIME ZONE you will see time jump around at daylight saving
time boundaries, and you will tend to have ambiguous times after the
clock has moved backward, where you can't tell what actual moment is
intended.

As you noted, now() is TIMESTAMP WITH TIME ZONE -- it has to be,
because it represents a moment in time.

-Kevin