>> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
