Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>>> select '[ 2 , NULL )'::int4range;
>>> ERROR: NULL range boundaries are not supported
>>> LINE 1: select '[ 2 , NULL )'::int4range;
>>
>> I think this might require more opinions. There is a trade-off
>> here between convenience and confusion: accepting NULL is
>> convenient in the constructors, because it avoids the need to
>> have extra constructors just for unbounded ranges; but could lead
>> to confusion between NULL and INF (which are not the same).
>
> I agree with this line of reasoning. I think we will be making
> pain for ourselves if we need to invent a bunch more constructors
> just to have a way of indicating an unbounded range, but OTOH I
> don't see any compelling reason why the type input function needs
> to accept N-U-L-L.
FWIW, the existing semantics of NULL include not only "UNKNOWN" but
also "NOT APPLICABLE". It seems fairly natural to think of a range
as being unbounded if the boundary limit is "not applicable".
On a practical level, our shop is already effectively doing this.
We have several tables where part of the primary key is "effective
date" and there is a null capable "expiration date" -- with a NULL
meaning that no expiration date has been set. It would be nice to
be able to have a "generated column" function which used these two
dates to build a range for exclusion constraints and such.
-Kevin