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