'Infinity' in date columns?
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to
say that the special value 'infinity' ("later than all other time stamps")
should work for an date-time column, and the type "date" is listed as among
the date-time data types.
But I can't get 'infinity' to work for columns of type "date".
Here's a test case:
CREATE TABLE _test
(
timestampx timestamp without time zone DEFAULT 'infinity'::timestamp
without time zone,
datex date DEFAULT 'infinity'::timestamp without time zone
)
);
INSERT INTO _test DEFAULT VALUES;
SELECT * FROM _test;
This returns (as viewed through pgAdmin III):
timestampx datex
----------- -----
infinity
When I tried to declared the "datex" column like this:
datex date DEFAULT 'infinity'::date
I got this:
ERROR: invalid input syntax for type date: "infinity"
Is there any way to get 'infinity' to work for a "date" column?
Or any other way to get the equivalent functionality in a date column?
~ TIA
~ Ken
On Mon, May 17, 2010 at 1:17 PM, Ken Winter <ken@sunward.org> wrote:
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to
say that the special value ‘infinity’ (“later than all other time stamps”)
should work for an date-time column, and the type “date” is listed as among
the date-time data types.But I can’t get ‘infinity’ to work for columns of type “date”.
I don't have version 8.3 with me right now but I just gave it a try with 8.4
and it gave me the expected output:
postgres=# CREATE TABLE _test
postgres-# (
postgres(# timestampx timestamp without time zone DEFAULT
'infinity'::timestamp without time zone,
postgres(# datex date DEFAULT 'infinity'::timestamp without time zone
postgres(# );
CREATE TABLE
postgres=# INSERT INTO _test DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT * FROM _test;
timestampx | datex
------------+----------
infinity | infinity
(1 row)
postgres=# select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 8.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
4.4.1-4ubuntu9) 4.4.1, 32-bit
(1 row)
--
Shoaib Mir
http://shoaibmir.wordpress.com/
"Ken Winter" <ken@sunward.org> writes:
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to
say that the special value 'infinity' ("later than all other time stamps")
should work for an date-time column, and the type "date" is listed as among
the date-time data types.
But I can't get 'infinity' to work for columns of type "date".
You seem to be carefully reading around the middle column in Table 8-13,
which specifically shows that infinity doesn't work for type date.
Now, if you were to update to Postgres 8.4, it *would* work.
regards, tom lane
Ken Winter wrote:
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
seems to say that the special value ‘infinity’ (“later than all
other time stamps”) should work for an date-time column, and the
type “date” is listed as among the date-time data types.But I can’t get ‘infinity’ to work for columns of type “date”.
Shoaib Mir wrote:
I don't have version 8.3 with me right now but I just gave it a try with
8.4 and it gave me the expected output:
As Tom Lane points out, that's a difference between 8.3 and 8.4.
--
Lew
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Tom Lane
...
"Ken Winter" <ken@sunward.org> writes:
The documentation at
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seemsto
say that the special value 'infinity' ("later than all other time
stamps")
should work for an date-time column, and the type "date" is listed as
among
the date-time data types.
But I can't get 'infinity' to work for columns of type "date".You seem to be carefully reading around the middle column in Table 8-13,
which specifically shows that infinity doesn't work for type date.
Oh, duh, right you are...
Now, if you were to update to Postgres 8.4, it *would* work.
I'll see if I can get my host to do that.
~ Thanks
~ Ken