Re: How to specify infinity for intervals ?

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to specify infinity for intervals ?
Date: 2006-02-21 03:24:09
Message-ID: 8F5E5AE0-3A36-49D8-A68F-F55945748A0D@myrealbox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2006-02-21 05:00:09 Re: How to determine number of established connections
Previous Message Tatsuo Ishii 2006-02-21 01:27:15 Re: Mixing different LC_COLLATE and database encodings