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

On 07/16/2012 07:41 PM, Alban Hertroys wrote:
>>> 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.
> Tom is telling you that there is a special "timestamp" 'infinity':
>
> alter table the_table alter column the_column set default
> tsrange(now()::timestamp without time zone, 'infinity'::timestamp
> without time zone);
>

Yup. The 'infinity' value doesn't play well with all database access
APIs and languages, though. Many languages can't represent infinite
dates, so the DB access APIs have to use dirty hacks with placeholder
values. It can be safer not to use infinite dates. Java and Python are
two languages that I know don't have infinite date representations (even
JodaTime doesn't add one for Java, grr!).

eg:

#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("dbname=postgres")
curs = conn.cursor()
curs.execute("SELECT DATE 'infinity';")
curs.fetchall()
[(datetime.date(9999, 12, 31),)]

The trap here is if you fetch some data, modify it, and push it back to
the DB, your 'infinite' dates might not be anymore. You have to trust
the database access layer to translate their placeholders back to
'infinity' and many won't.

That's where the other part of Tom's comment comes in: *poorly thought
out* substitute for infinity. Remember y2k? If you're going to use a
future date for 'infinity', try one that's nice and far away like
2999-01-01 . Consider adding a CHECK constraint that excludes dates
several decades prior, and all dates after, making it really obviously a
special value, eg:

CHECK date_in_range (  some_col < DATE '2300-01-01' OR some_col = DATE
'2999-01-01' )

... and DOCUMENT THIS CLEARLY in your app's limitations.

--
Craig Ringer