Re: Another question about Range types

>> There's another ongoing thread about range types, which was great because
>> I wasn't familiar with the feature (guess it's new in 9.2?).
>>
>> I run a recipe website and was looking for *exactly* this sort of feature
> a few
>> weeks ago when I was adding in support for ranges of ingredients (such as
>> "1-2tsp salt"). In the end, I implemented it using two columns (QtyHigh
> and
>> QtyLow). In the salt example, QtyHigh would be 2 and QtyLow would be 1.
> I
>> also have some CHECK constraints to make sure high is always higher, and
>> they're not the same, and not negative or anything.
>>
>> Now, for 99% of the ingredients, QtyHigh would have a value and QtyLow
>> would just be null. For example, "2tsp salt" would have a QtyHigh of
>> 2 and a QtyLow of null, which would indicate there is no range.
>>
>> I'm curious if I could combine these columns into one using a RANGE type.
>> Obviously, if the column only stored ranges, it would be easy.
>> However, can a range also be one-dimensional? Can I have a High value and
>> no low value? Or would the recommended design be to have high/low be
>> the same? Overall, would this scenario be an appropriate use case for
> this
>> RANGE type, since only some of the data are ranges?
>>
>> BONUS Question:
>>
>> How are RANGE types represented in Npgsql, or are they even supported
>> yet?
>>
>
> Given my lack of experience in the cooking domain my opinion has limitations
> but if you want to encode the quantity as a range a specific value should be
> encoded as "[2, 2]".
>
> While I am not morally opposed to NULL it is best to avoid introducing them
> whenever it is possible to do so. In this case it is correct as well since
> you know what the lower bound on quantity is, it is 2tsp.
>
> Thus your CHECK constraint is incorrect. You should allow for the values to
> be equal. Non-negative is good but it should be "L <= H".
>
> The absence of a value in the range implies that the range is unbounded on
> that end. There is no way to actually store a "NULL" in the range - any
> attempt to do so will simply result in that side of the range being
> unbounded instead.
>
> http://www.postgresql.org/docs/9.2/static/rangetypes.html

Yea, I agree with all of this. I did consider storing "2tsp" as a
High of 2 and a Low of 2, but it seemed kinda odd to store the same
data twice. However, from a mathematical point of view, it is
accurate to say "use between 2 and 2 tsp of salt".

If I do switch to RANGE types, I think [2,2] would make sense in this
case. Using unbounded ranges might make sense if I wanted to express
something like "Use up to 1 cup of flour" or "You'll need at least 3
cups of water".

I'm not ready to use 9.2 in production yet, but I will definitely do
some more testing on this subject when 9.2 is released and stable.
Thanks!

Mike