Another question about Range types

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:

Done. Bug #1011216

On Fri, Aug 3, 2012 at 1:11 PM, Francisco Figueiredo Jr.
<francisco.figueiredo.jr@gmail.com> wrote:

Thanks!
Em 03/08/2012 17:25, "Mike Christensen" <mike@kitchenpc.com> escreveu:

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

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.