PostgreSQL Discussion - How to specify infinity for intervals ?

Author

How to specify infinity for intervals ?

Karsten Hilbert

2006-02-20, 1:23 pm

I am storing the maximum age a vaccination is due in a
patient (eg. "don't give this vaccination beyond the age of
10 years" or some such). Some vaccinations are to be given
regardless of age.

Up to now I have used NULL to mean "no maximum age". That
doesn't really feel right and also complicates the SQL
needed for retrieving data.

I *could*, of course, use something like '999 years' as a
special value to indicate "no upper limit" figuring that no
one is going to live that long in the foreseeable future.

However, the technically elegant and satisfying solution
would be to be able to use "infinite" with interval data
types much like "infinity" with timestamps. I have tried
various syntax attempts, calculations and casts but haven't
found any returning an interval of infinite length. The docs
and Google don't help, either.

I am running 7.4.9 on Debian 4.0.

Anyone wants to comment/suggest something ?

Thanks,

Karsten
GNUmed developer
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Michael Glaesemann

2006-02-21, 3:23 am

On Feb 21, 2006, at 3:24 , Karsten Hilbert wrote:

> I am storing the maximum age a vaccination is due in a
> patient (eg. "don't give this vaccination beyond the age of
> 10 years" or some such). Some vaccinations are to be given
> regardless of age.
>
> Up to now I have used NULL to mean "no maximum age". That
> doesn't really feel right and also complicates the SQL
> needed for retrieving data.

I don't know the details of your database schema, but I think the
relationally proper way to do would be to have a separate table for
the maximum ages for vaccinations that have them. Vaccinations that
*don't* have a maximum age would not have an entry in the table. For
example:

create table vaccinations
(
vaccination text primary key
);

create table vaccination_max_ages

(
vaccination text primary key
references vaccinations (vaccination)
, maximum_age interval not null
);

This may make the SQL a bit more complicated, and you may end up
doing quite a few left joins which will give you NULLs anyway in the
result unless you use COALESCE. From one perspective (though not one
I necessarily agree with), using NULL to represent "no maximum age"
in the vaccinations table is a shorthand for this situation and
reduces the number of joins required.

The "special value" method, e.g., "999 years" is another way of
indicated a special value, but in this case I think it's a bit
different. As I see it, the predicate for the vaccination_max_ages

table is "The vaccination 'vaccination' must be given before the
patient is 'maximum_age'". Using a special value changes this
predicate to "The vaccination 'vaccination' can be given at any time
in the patient's life." As you point out, using a sufficiently large
interval for maximum_age makes that statement very likely to be true,
but the predicate is not exactly the same. Not having an entry in
vaccination_max_ages
is much closer to the idea that the vaccination
has no maximum age.

That's the theory, anyway. Hope this helps a bit.

Currently on the todo list there's a mention of adding infinite
dates, similar to infinite timestamps. Perhaps infinite intervals
could be added as well?

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Bruno Wolff III

2006-02-25, 9:44 am

On Mon, Feb 20, 2006 at 19:24:05 +0100,
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> I *could*, of course, use something like '999 years' as a
> special value to indicate "no upper limit" figuring that no
> one is going to live that long in the foreseeable future.

I would think that specifying a value well beyond the current life
expectency for people would be the simplest solution. Since it doesn't
have to be treated like a magic value and can used consistantly with
other values in the same column.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-02-25, 9:44 am

On Mon, Feb 20, 2006 at 07:24:05PM +0100, Karsten Hilbert wrote:
> I am storing the maximum age a vaccination is due in a
> patient (eg. "don't give this vaccination beyond the age of
> 10 years" or some such). Some vaccinations are to be given
> regardless of age.
>
> Up to now I have used NULL to mean "no maximum age". That
> doesn't really feel right and also complicates the SQL
> needed for retrieving data.
>
> I *could*, of course, use something like '999 years' as a
> special value to indicate "no upper limit" figuring that no
> one is going to live that long in the foreseeable future.
>
> However, the technically elegant and satisfying solution
> would be to be able to use "infinite" with interval data
> types much like "infinity" with timestamps. I have tried
> various syntax attempts, calculations and casts but haven't
> found any returning an interval of infinite length. The docs
> and Google don't help, either.

I suspect that you could create either a domain or a custom type that
would handle this they way you wanted.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Karsten Hilbert

2006-02-25, 9:44 am

Thanks to all for the suggestions.

For the time being I will stay with using NULL.

I will also stay with the hope that one day before long we
will have " 'infinite'::interval
".

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Bruce Momjian

2006-02-25, 9:44 am

Karsten Hilbert wrote:
> Thanks to all for the suggestions.
>
> For the time being I will stay with using NULL.
>
> I will also stay with the hope that one day before long we
> will have " 'infinite'::interval
".

We have this TODO:

o Allow infinite dates just like infinite timestamps

Do we need to add intervals to this?

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Michael Glaesemann

2006-02-25, 9:44 am

On Feb 25, 2006, at 12:09 , Bruce Momjian wrote:

> We have this TODO:
>
> o Allow infinite dates just like infinite timestamps
>
> Do we need to add intervals to this?

I think to be consistent with the other datetime types, might as
well. Then one would be able to get an answer to

test=# select 'infinity'::timestam
p - current_timestamp;
ERROR: cannot subtract infinite timestamps

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Bruce Momjian

2006-02-28, 8:28 pm

OK, added to TODO.

---------------------------------------------------------------------------

Michael Glaesemann wrote:
>
> On Feb 25, 2006, at 12:09 , Bruce Momjian wrote:
>
>
> I think to be consistent with the other datetime types, might as
> well. Then one would be able to get an answer to
>
> test=# select 'infinity'::timestam
p - current_timestamp;
> ERROR: cannot subtract infinite timestamps
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Karsten Hilbert

2006-02-28, 8:28 pm

On Fri, Feb 24, 2006 at 10:09:25PM -0500, Bruce Momjian wrote:

> Karsten Hilbert wrote:
>
> We have this TODO:
>
> o Allow infinite dates just like infinite timestamps
>
> Do we need to add intervals to this?
Yes. Thanks.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links