Thread: 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?

Mike

Done.  Bug #1011216

On Fri, Aug 3, 2012 at 1:11 PM, Francisco Figueiredo Jr.
<francisco.figueiredo.jr@gmail.com> wrote:
>
> Regarding npgsql, range types have no direct mapping so they will be treated
> like strings. Sorry for that.
>
> I'll work to add support to it. Would you mind to fill a feature request
> about that on our feature requests project page? http://project.npgsql.org
>
> Thanks in advance.
>
> Em 03/08/2012 17:01, "Mike Christensen" <mike@kitchenpc.com> escreveu:
>>
>> 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?
>>
>> Mike
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

>> 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

>
> 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".
>

In these cases:

Flour: [0, 1] - "optional, but maximum of 1-cup";  you cannot supply a
negative amount of ingredient...
Water: [3, ) - "at least three cups, but remember you will have to pay the
water bill at some point..."

The water example implies some need to flag the quantity as needing an
explanation.  In this case I would probably say "[3,3]" and set a flag so
that you can describe, in words, those situations where additional water
would be OK.  Mabye some kind of "quantity_precision" flag with possible
values of [EXACT,APPROXIMATE,MINIMUM,MAXIMUM].  The reason I say this is
because the value of storing an amount as a value is that it can be used in
calculations and it is difficult to use infinity in calculations.  By
storing a finite range you can more readily use the values in calculations
and when you care about whether the value is "required" or "suggested" you
have a flag you can query to tell you which it is.

David J.

>> 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".
>>
>
> In these cases:
>
> Flour: [0, 1] - "optional, but maximum of 1-cup";  you cannot supply a
> negative amount of ingredient...
> Water: [3, ) - "at least three cups, but remember you will have to pay the
> water bill at some point..."
>
> The water example implies some need to flag the quantity as needing an
> explanation.  In this case I would probably say "[3,3]" and set a flag so
> that you can describe, in words, those situations where additional water
> would be OK.  Mabye some kind of "quantity_precision" flag with possible
> values of [EXACT,APPROXIMATE,MINIMUM,MAXIMUM].  The reason I say this is
> because the value of storing an amount as a value is that it can be used in
> calculations and it is difficult to use infinity in calculations.  By
> storing a finite range you can more readily use the values in calculations
> and when you care about whether the value is "required" or "suggested" you
> have a flag you can query to tell you which it is.

Yea, I wrote a blog post on this subject recently if you're interested.

http://blog.kitchenpc.com/2012/06/27/3-4-things-left-to-do/

The gist is that I needed to store unknown amounts (such as "pepper to
taste") as well as ranges of amounts.  Trying to represent that in
Postgres indeed brought up some controversial design decisions, which
led to this StackOverflow question (which had a lot of DB purists
yelling at me!)

http://stackoverflow.com/questions/11439796/how-can-i-encode-two-numbers-in-a-single-integer

Really, the only thing I do with the data is total up amounts to
generate a shopping list.  With ranges, I simply take the high amount.
 If one recipe calls for 2-3 eggs, and another calls for 4 eggs, I
would add 7 eggs to the shopping list as that's the most you'd need to
buy.  The "Low" amount is simply for display purposes only, I do no
math with this.  However, I can't predict what the future holds for
the site.  Maybe I'll need to store all sorts of crazy things, so it's
best to handle things in the best way up front so I don't have to rip
apart all this low level code later on.

Mike

Didn't feel like creating an account (or figuring out my logon info) for the
post so I'll include my thoughts here:

> Yea, I wrote a blog post on this subject recently if you're interested.
>
> http://blog.kitchenpc.com/2012/06/27/3-4-things-left-to-do/
===============================================
    Salt & Pepper is a sub-type of "spice" where generally the chef would
either have some available - or not.

    3 eggs + "eggs" = "3+ eggs"

    I see three categories of measurement:
    Known
    Unmeasured (i.e., "to taste")
    Measured - Unknown

    And two categories for ingredients:
    Unit
    Bulk

    Unit items are those where you can readily count how many you have/need;
generally purchased and used within a handful of meals.

    Bulk items are those, like spices, that you buy enough for many meals.
You measure when you use them but otherwise do not keep track of how much is
remaining.

    I could see the presentation interface having multiple columns/sections
each listing the appropriate ingredients.

    In searching for ranges I think:
    \d+\s*(-|to)\s*\d+
    would be a better expression (capture the word "to" as well as "-")

    Basically a divide-and-conquer strategy. Classify, find differences and
similarities, then decide how to present them to the user.

Also, consider the following:

WITH ruleset (regex_exp) AS ( VALUES ('exp1'), ('exp2') )
SELECT data, regex_emp
FROM ruleset
CROSS JOIN (SELECT 'data' AS data) dt
WHERE dt ~ regex_exp;

This returns one record for each expression that matches "data".  I use this
general technique to build a regex-based rule engine in postgresql.
Generally you need to rank the rules and then return the match with the
lowest/highest rank.

////////////////////////////

> Really, the only thing I do with the data is total up amounts to generate
a
> shopping list.  With ranges, I simply take the high amount.
>  If one recipe calls for 2-3 eggs, and another calls for 4 eggs, I would
add 7
> eggs to the shopping list as that's the most you'd need to buy.  The "Low"
> amount is simply for display purposes only, I do no math with this.
However,
> I can't predict what the future holds for the site.  Maybe I'll need to
store all
> sorts of crazy things, so it's best to handle things in the best way up
front so I
> don't have to rip apart all this low level code later on.


==============================

Please do not suggest that people should buy an infinite number of eggs...
:)

David J.

> -----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.

Thanks! 

Regarding npgsql, range types have no direct mapping so they will be treated like strings. Sorry for that.

I'll work to add support to it. Would you mind to fill a feature request about that on our feature requests project page? http://project.npgsql.org

Thanks in advance.