On Friday, 19 August 2011, Erik Hesselink wrote:

Do you really need the precision info about the column, or do you just need the values at the right precision? Because you get the last thing already:

Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(5,0);" []) :: IO Rational 1 % 1 Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(5,4);" []) :: IO Rational 1231 % 1000

I'm not sure I understand the distinction --- to my way of thinking, getting the value at the right precision means getting the correct number of significant decimal digits, which both your example and mine fail to provide. Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(10,4);" []) :: IO Rational -- gives 1231 % 1000 == 1.231 in decimal notation Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(10,8);" []) :: IO Rational -- still gives 1231 % 1000 but should be 1.21310000 in decimal notation -- or 1231000 % 1000000 in rational notation

If you need the precision information, perhaps 'describeResult' will give you what you need. I've never used it, but it looks like it might.

Thanks for the suggestion. That will work, but ideally i'd like to have the normal usage of quickQuery, etc return correct data. Unless I am missing something here, the method of getting precision information using describeResult would be to fetch the result set, then find any columns having a numeric(x,y) SQL type and transform them. Even just getting the raw string response from the RDBMS would be better than that since the raw response is actually already a correctly formatted string representation of the data so I could use it for output without any further transformation. Is there a way to get raw response data from the RDBMS through HDBC? -- Henry House +1 530 848-1238

On Friday, 19 August 2011, Erik Hesselink wrote:

On Fri, Aug 19, 2011 at 16:09, Henry House wrote:

...

On Friday, 19 August 2011, Erik Hesselink wrote:

...

Do you really need the precision info about the column, or do you just need the values at the right precision? Because you get the last thing already:

Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(5,0);" []) :: IO Rational 1 % 1 Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM` (quickQuery db "select 1.231 ::numeric(5,4);" []) :: IO Rational 1231 % 1000

I'm not sure I understand the distinction --- to my way of thinking, getting the value at the right precision means getting the correct number of significant decimal digits, which both your example and mine fail to provide.

Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM`   (quickQuery db "select 1.231 ::numeric(10,4);" []) :: IO Rational -- gives 1231 % 1000 == 1.231 in decimal notation Prelude Database.HDBC.PostgreSQL Database.HDBC Data.Ratio Control.Monad> (fromSql . head . head) `liftM`   (quickQuery db "select 1.231 ::numeric(10,8);" []) :: IO Rational -- still gives 1231 % 1000 but should be 1.21310000 in decimal notation -- or 1231000 % 1000000 in rational notation

The % notation is a rational, so 'infinite' precision. So '1 % 1' and '1000 % 1000' are exactly the same, semantically. It's like fractions instead of decimal digits.

You're right, of course. My example was something of an abuse of notation to include a notion of precision where it is actually an incompatible concept.

Why exactly do you need the precision information?

Empirical measurements (e.g., sizes of some fields in hectares) are precise only to a certain level of measurement error. Thus, the area measurements 1 ha and 1.000 ha are not equivalent or interchangeable. Database engines recognize this fact by providing different data types for rational numbers and fixed-precision decimal numbers. The bottom line for me is that the conversion of a fixed-precision decimal number as a rational is both throwing away information (the precision) as well as introducing bogus information (the notion that the result value has greater --- i.e., infinite --- precision that was in fact intended when that value was stored). -- Henry House +1 530 848-1238

On Fri, Aug 19, 2011 at 11:45, Erik Hesselink wrote:

Note that PostgreSQL also doesn't work with decimals as precision:

postgres=# select 1::decimal(4,2) * 1::decimal(4,2); ?column? ---------- 1.0000 (1 row)

That should be 1.00 instead if you want the precision correctly represented.

Er? Last I checked, that was exactly how precision worked over multiplication; otherwise you are incorrectly discarding precision present in the original values. Unless you're assuming the OP actually wants an incorrect flat precision model.... -- brandon s allbery allbery.b@gmail.com wandering unix systems administrator (available) (412) 475-9364 vm/sms

On 20/08/2011, at 11:41 PM, Erik Hesselink wrote:

This is the way I was taught to do it in physics. See also http://en.m.wikipedia.org/wiki/Significance_arithmetic

There are at least two different "readings" of fixed precision arithmetic. (1) A number with d digits after the decimal point is a *precise* integer times 10**-d. Under this reading, scale(x) ± scale(y) => scale(max(x,y)) scale(x) × scale(y) => scale(x+y) scale(x) ÷ scale(y) => an exact rational number scale(x) < scale(y) is well-defined even when x ~= y scale(x) = scale(y) is well-defined even when x ~= y (2) A number with d digits after the decimal point represents *some* number in the range (as written) ± (10**-d)/2 Under this reading, scale(x) ± scale(y) => scale(min(x,y)) scale(x) × scale(y) => depends on the value of the numbers scale(x) < scale(y) is often undefined even when x = y scale(x) = scale)y) is often undefined even when x = y The web page Erik Hesselink pointed to includes the example 8.02*8.02 = 64.3 (NOT 64.32). Values in data bases often represent sums of money, for which reading (1) is appropriate. One tenth of $2.53 is $0.253; rounding that to $0.25 would in some circumstances count as fraud. Of course, values in data bases often represent physical measurements, for which reading (2) is appropriate. There is, however, no SQL data type that expresses this intent.

On Friday, 19 August 2011, Erik Hesselink wrote: [...]

Perhaps a solution would be to not treat the database precision as your primary source of information, but represent that in Haskell using some data type that correctly propagates precision information, and marshall your database data to and from that. This means some duplication of information (precision in both database and Haskell) but you do the same with NULL and Maybe, etc. I guess that's inherent to (the way HDBC does) database access.

That's exactly what I'm after: getting the data out of the db as a Haskell data type that preserves the precision information (which the use of Rational does not). -- Henry House +1 530 848-1238

On Friday, 19 August 2011, Brandon Allbery wrote:

On Fri, Aug 19, 2011 at 10:09, Henry House wrote:

...

I'm not sure I understand the distinction --- to my way of thinking, getting the value at the right precision means getting the correct number of significant decimal digits, which both your example and mine fail to provide.

So you want display format data instead of something that you can do computations with? HDBC is giving you the latter; the precision is correct for computation, although it could be argued that a Fixed type would be better if you intend to propagate exact precision through operations. (That said, I believe Fixed obeys exact mathematical precision instead of replicating your SQL backend's limitations, so you'd be unhappy again in case of multiplication or division.)

No, I want a data type in Haskell that mirrors the data type in the RDBMS, not conversion of RDBMS fixed-precision data into something else (Rational). If the data in the RDBMS represented a rational number of arbitrary precision, then they would have been stored in the RDBMS as a rational data type (assuming that the database was designed sensibly, of course). The limitations of the SQL backend are deliberately chosen (no one is forced to use fixed-precision numeric data types in PostgreSQL; arbitrary-precision numeric is available as well as a true rational with a contrib module). I'm well aware that management of roundoff error in computations is a tricky issue that needs appropriate management but that's not really my issue here.

I would suggest that if you want SQL formatted string output, you describe that in the query instead of expecting HDBC to convert numeric data to string while duplicating your SQL backend's formatting.

That is a reasonable suggestion. Even better would be to read the result set returned by the SQL backend without any type conversion being done. AFAIK all result sets are returned as formatted text. The representation of result sets as booleans, integers, etc, is a result of conversion in the client.

Otherwise, perhaps you'd be better suited to a language with a looser type system, so you can pretend string and numeric values are the same thing and usually get something resembling the right result.

I apologize, I can see that I was not clear at all in my question. This is quite the opposite of what I am looking for: I would like a stricter interpretation of data types --- that does not treat fixed-precision and rational as the same thing --- not a looser one. Anyway, while I appreciate the discussion about significant figures on computation, etc, I was asking whether anyone can point me in the right direction for implementing a custom SQL-to-Haskell type conversion, replacing the built-in behavior of HDBC (numeric fixed-precision to SqlRational) with a conversion that better suits my needs (e.g., as provided by the Decimal library). Thanks. -- Henry House

On 21/08/2011, at 3:01 AM, Henry House wrote:

No, I want a data type in Haskell that mirrors the data type in the RDBMS, not conversion of RDBMS fixed-precision data into something else (Rational). If the data in the RDBMS represented a rational number of arbitrary precision, then they would have been stored in the RDBMS as a rational data type (assuming that the database was designed sensibly, of course).

How? There is no rational data type in standard SQL. "Arbitrary precision" really does not fit the classic SQL model of fixed size columns very well.

The limitations of the SQL backend are deliberately chosen (no one is forced to use fixed-precision numeric data types in PostgreSQL; arbitrary-precision numeric is available as well as a true rational with a contrib module).

Can you talk to the designer of the data base in question? Do you know whether they were interested in exploiting features of PostgreSQL or whether they were interested in portability? Are you certain that 2.00 will always be reported as "2.00" and never as "2.0"?