infinity interval

On Sat, Dec 20, 2008 at 3:52 PM, Gerhard Heift
<ml-postgresql-20081012-3518@gheift.de> wrote:

how can I store an infinity value into an interval?

I don't think you can:

postgres=# select INTERVAL '1 week' + 'infinity'::timestamp;
?column?
----------
infinity
(1 row)

postgres=# select INTERVAL 'infinity' + now();
ERROR: invalid input syntax for type interval: "infinity"

Also notice the limits that the Interval datatype supports.
http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html

I want to store
offsets to a timestamp, and for some cases i need +infinity and
-infinity as result.

You might need to rethink your design to two timestamps, or use a null
represent to concept of infinite duration since postgresql doesn't
currently support this.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Gerhard-
did you try to use a PL/SQL Block to initialize the necessary integer values in your declare block e.g.
DECLARE
SET @INT_MIN = INTEGER ( min ); --Min Value
SET @INT_MAX = INTEGER ( max ); --Max value
BEGIN
....
END;

http://pgscript.projects.postgresql.org/SCRIPT.html
HTH
Martin

______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

Date: Sun, 21 Dec 2008 00:52:04 +0100
From: ml-postgresql-20081012-3518@gheift.de
To: pgsql-general@postgresql.org
Subject: [GENERAL] infinity interval

Hello,

how can I store an infinity value into an interval? I want to store
offsets to a timestamp, and for some cases i need +infinity and
-infinity as result.

I want to make something like this:
SELECT now() + 'infinity'::interval;

This should return 'infinity'::timestamp.

Is it possible?

Thanks,
Gerhard

_________________________________________________________________
Send e-mail faster without improving your typing skills.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008

Gerhard Heift wrote:

how can I store an infinity value into an interval? I want to store
offsets to a timestamp, and for some cases i need +infinity and
-infinity as result.

I want to make something like this:
SELECT now() + 'infinity'::interval;

This should return 'infinity'::timestamp.

Is it possible?

TODO has:

o Allow infinite intervals just like infinite timestamps

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +