Re: Range Types - typo + NULL string constructor

Robert Haas <robertmhaas@gmail.com> wrote:
> Jeff Davis <pgsql@j-davis.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