Problems on NUMERIC - Mailing list pgsql-hackers

From jwieck@debis.com (Jan Wieck)
Subject Problems on NUMERIC
Date
Msg-id m0zsNeF-000EBUC@orion.SAPserv.Hamburg.dsh.de
Whole thread Raw
List pgsql-hackers
Hi,

    sometimes it's good not to spend too much efford implementing
    the final solution first. So for the NUMERIC.

    First I wonder why the can_coerce... stuff is  #if'd  out  of
    parse_relation.c?     For     the     NUMERIC     type    the
    numeric(num,typmod) must be called if someone does an

        INSERT INTO ... SELECT * FROM ...

    But it isn't. It is only called when there  are  calculations
    done  on the columns. I also checked that for BPCHAR type and
    it simply throws an ERROR  if  the  target's  length  doesn't
    match.

    This  might be easy to fix, but the other problem I have is a
    bit more difficult.

    When binary operators (add, sub, mul, div)  are  called,  the
    required  precision of the result isn't known. And the coerce
    function numeric(num,typmod) will  only  be  called  for  the
    final result. Now have the following situation:

    CREATE TABLE t1 (id int4, annual_val numeric(20,4));
    CREATE TABLE t2 (id int4, monthly_val numeric(24,8));

    INSERT INTO t2 SELECT id, annual_val / '12' FROM t1;

    A  multiplication  would have a maximum number of digits that
    can appear after the decimal point. It is the sum  of  number
    of  digits  present  in  the  two operators. But not so for a
    division.

    If we want to implement NUMERIC with a  real  high  precision
    (maybe  4000  or  more  digits),  there would currently be no
    other chance than to do  the  division  with  the  full  ever
    possible  precision  and  then  throw away most of the digits
    when the result is assigned  to  the  target  column.  Wasted
    efford and more important MUCH WASTED CPU.

    I can think of something like this:

    On add/subtract the results precision after the decimal point
    is the higher of the two operands.

    On multiply the results precision after the decimal point  is
    the sum of the precisions of the two operands.

    On  divide  the  results precision after the decimal point is
    like for mult or the double of the higher  precision  of  the
    two operands. Any other suggestions?

    On the other hand it is possible to do it as

    INSERT INTO t2 SELECT id, ROUND(annual_val,8) / '12' FROM t1;

    How  do  other  databases  handle  this  problem.  How is the
    precision of a numeric result defined?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

pgsql-hackers by date:

Previous
From: Oleg Broytmann
Date:
Subject: Re: [HACKERS] PostgreSQL 6.4 like bug(?)
Next
From: "Thomas G. Lockhart"
Date:
Subject: Re: [HACKERS] Problems on NUMERIC