[Haskell-cafe] custom SQL-to-Haskell type conversion in HDBC

[Henry House]

On Friday, 19 August 2011, Erik Hesselink wrote:
> On Fri, Aug 19, 2011 at 16:09, Henry House <hajhouse at hajhouse.org> 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