Hi,

There are a bunch of "issues" here but I will try to
reduce my question to the most important one(s)...
[I sincerely hope I haven't asked this before]

Almost *every* numeric datum in my tables is a
dimensioned quantity. E.g., "3 inches", "6 pounds",
"14.7 lumens", etc.

A column's "dimension" is consistent for all values
within that column. E.g., a column may have the
dimension of "length". So, "3 feet", "1 yard",
"2 meters", "83.5 parsecs" would all be valid values
for such a column.

[note that "3 feet" is different from "1 yard" -- even
though they both might *resolve* to the same physical
length]

The first "problem" is to define a mechanism whereby I
can enforce a particular "dimensioned type" on a column.
So, I can define a column as "type length" and, thereafter,
be assured that PG (with my mechanisms) enforces this
constraint on all values entered into that column.

I can come up with a representation that allows me to
accurately preserve values as entered (e.g., noting that
"3.0 feet", "36 inches", "1 yard" and "3 feet" are each
*different* and need to be recalled as such) yet gives
me some expediency in comparison operations (e.g., for
sorting).

[I didn't claim this was going to be *efficient*! :> ]

So, for example, I can store the "input string", as entered,
to preserve the subtleties in the original representation
(e.g., "3 ft" vs. "3.0000 ft" vs. "1 yd" vs. "2 ft 12 in"!).

I can come up with a "sorting value" for the cardinality of
the value represented (in bogounits).

I can parse the string (prior to "blessing it") to verify the
dimensions within the string are consistent among themselves
(e.g., "2 ft 12 in" is OK but "2 ft 3 oz" is not).

I can codify the overall "dimension" of the value (e.g., "length"
vs. "angle" vs. "length / time").

And, of course, I can compare this with the "defined type" of
the column to verify that the value entered is appropriate
for this column.

But, where should I *keep* that "definition"? I.e., it does
not vary on a row by row basis so it doesn't deserve to be part
of the individual data. And, it is "defined" before any
*values* are inserted into that column (i.e., it's definition
exists even in the absence of data).

I suspect twiddling the system tables is frowned upon?
But, I could always create my *own* version of the system
tables layered atop everything??

(sigh) Too early in the morning for this sort of thinking.
My head hurts... :-(

Thanks!
--don

On 2011-03-01, D Yuniskis <not.going.to.be (AT) seen (DOT) com> wrote:
1 way: check the value against the apropriate regular expression.

another way: attempt to use 'units' or a similar program to convert
the offered value into a chosen base type (eg: for length convert it to
metres)

something like 'units'? or design your own if you don't need obscure
units like 'thomb'

$ units 'three inch' mm
* 76.2

A c function (or several) using libudunits might be the way
to go. I have never used libudunits, 'units' does not use it either.

just create several new type of text and new comparison operators
that convert to a base type before comparing.

absolute temperature will need a to be different type to relative temperature.

units takes " as seconds of arc rather than as inches, but it may be
possible to force libudunits to do what you want.

--
⚂⚃ 100% natural

Hi Jasen,

On 3/9/2011 3:13 AM, Jasen Betts wrote:
That's not trivial. The grammar is something like:

(<value>(/<value>)? <unit>)+

But valid <unit> are defined, ultimately, in a table within
PG. Parsing an expression can require resolving a <unit>
to determine its compatibility with other <unit>s in the
same "expression".

As a (silly) example, the following is a valid entry:

5.2 g 17/3 ft / sec / sec 2 m / min / sec

since all units resolve to "length / (time * time)".

[I have no idea why anyone would opt to specify a
quantity this bizarrely but it makes as much sense
as "3 ft 2 in"]

I don't resolve an expression to a "real" value because
this throws away information. E.g., "3" and "3.000" are
different as are "12 inches" and "1 foot" (neglecting the
ambiguity in determining *which* "foot" is meant, here).

I thought I could possibly store an *approximation* of
a "real value" as an *interval* (i.e., (min,max) range)
and use that to expedite comparisons. So, the compare
operator that I would have to write for the type could
do something like:

if (a.min > b.max)
return A_GREATER_THAN_B
else if (a.max < b.min)
return A_LESS_THAN_B
else
/* ranges overlap so more expensive test required */

and *hope* that the early tests handle most comparisons.

These all force binding the expression to a real value
before storing it. See above

If I restrict the type to just a special "text", then I
lose the possibility of any optimizations for comparisons, etc.
(because there are no other "members" in the type that I
can stuff information into -- like "(min, max)" alluded to
above)

Yes. I'm not worried about handling the unit conversions, etc.
Rather, trying to let PG enforce what it *should* enforce
("this column has type 'temperature'; this other column
has type 'length / time'; etc.") in as efficient a way as
possible.

On 2011-03-10, D Yuniskis <not.going.to.be (AT) seen (DOT) com> wrote:
what I mean is attempt the conversion, and if that's successful the
expression is valid



floating-point types are by definition approximations.

where are you getting min and max from ?

no they don't. store the string, use units for the comparison.

$ units "6 miles - 10 kilometres"
Definition: -343.936 m

negative result means '10 kilometres' > '6 miles'

Why do you need "(min, max)"? the precision of a double precision float
exceeds the capability of all common measuring aparatus.

--
⚂⚃ 100% natural

On 3/10/2011 2:13 AM, Jasen Betts wrote:
Ah, OK.

Yes. min+1 ULP = max.

The point is, to not look for "true" comparisons unless you
are forced to do so (because the stored "approximation" is
ambiguous)

When the expression is initially parsed and it's representation
created, determine an interval (min,max) that approximates the
value FOR COMPARISONS.

I only want to do that for cases where the "stored approxmation"
is "ambiguous" -- it is too expensive to be doing *often* (and
I am gambling that the approximation might be "good enough"
for most compares)

Another approach is to store a sort index on each INSERT or UPDATE
assuming values are retrieved more often than altered/added.

Because expressions can express things that can't be
"measured"! :> Why not *legislate* pi to be equal to
"(double) pi"?

--don