> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Mike Christensen
> Sent: Friday, August 03, 2012 4:00 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 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
David J.
