Chris Bartlett <c.bartlett@xxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
