Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I see that Postgres has a special keyword "infinity" for use with
> timestamps. Is there an equivalent for dates?
There is not.
> I tried the following but it doesn't quite work:
> JC=# create table test(a date);
> CREATE TABLE
> JC=# insert into test values('infinity'::timestamp);
> INSERT 1030323 1
> JC=# select * from test;
> a
> ---
> (1 row)
It appears that what actually gets stored in test.a is NULL ... which
is not surprising given the source code for timestamp_date():
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_NULL();
I think that this is an outright bug: if type DATE doesn't have a
concept of infinity then it should throw an error, not translate
infinity to NULL. NULL means "unknown", not "I cannot cope with this
value".
Comments?
regards, tom lane