> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Mike Christensen > Sent: Friday, August 03, 2012 4:00 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
