# Re: [HACKERS] Question about the TODO, numerics, and division

```Here is the full TODO item:

* Add NUMERIC division operator that doesn't round?

Currently NUMERIC _rounds_ the result to the specified precision.
This means division can return a result that multiplied by the
divisor is greater than the dividend, e.g. this returns a value > 10:

SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;

The positive modulus result returned by NUMERICs might be considered
inaccurate, in one sense.```
```
The reason the TODO item was added is the last sentence, namely that you
can't use division to do a modulus.  This is fine:

test=> SELECT (10::numeric(2,0) / 6::numeric(2,0));
?column?
--------------------
1.6666666666666667
(1 row)

but this is where the rounding causes a problem:

test=> SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;
?column?
----------
12
(1 row)

When casting to numeric(2,0), the value is rounded up to '2', not '1'.
However, integer does the same thing:

test=> SELECT (1.66)::integer * 6;
?column?
----------
12
(1 row)

floor() works just fine:

test=> SELECT floor(10::numeric(2,0) / 6::numeric(2,0)) * 6;
?column?
----------
6
(1 row)

and there is a NUMERIC version of floor():

test=> \df floor
List of functions
Schema   | Name  | Result data type | Argument data types
------------+-------+------------------+---------------------
pg_catalog | floor | double precision | double precision
pg_catalog | floor | numeric          | numeric
(2 rows)

so I am removing the TODO item.  As you can see from the question mark
on the item, I was dubious of its validity.  It was added on 2005-06-25.

---------------------------------------------------------------------------

Tom Lane wrote:
> Chris Travers <[EMAIL PROTECTED]> writes:
> > I have been looking at the TODO and have found something that I find
> > sort of odd and we should probably reconsider:
>
> > One of the items under data types is:
>
> >     * Add NUMERIC division operator that doesn't round?
>
> >       Currently NUMERIC _rounds_ the result to the specified precision.
> >       This means division can return a result that multiplied by the
> >       divisor is greater than the dividend, e.g. this returns a value > 10:
> >       SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;
>
> I agree that the TODO item is pretty bogus as worded.  A closer look
> at what's going on is:
>
> regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) ;
>       ?column?
> --------------------
>  1.6666666666666667
> (1 row)
>
> and of course if you multiply that by 6 you get
>
> regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;
>       ?column?
> ---------------------
>  10.0000000000000002
> (1 row)
>
> However this seems basically insoluble.  The TODO item seems to imagine
> that it would be better if the division returned 1.6666666666666666,
> but AFAICS that answer is actually *less* accurate:
>
> regression=# select 1.6666666666666666 * 6;
>       ?column?
> --------------------
>  9.9999999999999996
> (1 row)
>
> regression=#
>
> The only way to make it more accurate is to return more decimal places,
> but you'll never get an exact result, because this is a nonterminating
> fraction.
>
> There may be a use for a division operator that rounds the last returned
> digit towards minus infinity instead of to nearest, but the TODO entry
> is utterly unconvincing as an argument for that.  Does anyone recall
> what the original argument was for it?  Perhaps the TODO entry is
> just mis-summarizing the discussion.
>
> A separate question is whether the division operator chooses a good
> default for the number of digits to return.  You can make it compute
> more digits by increasing the scale values of the inputs:
>
> regression=# SELECT (10::numeric(32,30) / 6::numeric(2,0)) ;
>              ?column?
> ----------------------------------
>  1.666666666666666666666666666667
> (1 row)
>
> but I wouldn't want to defend the details of the rule about how many
> fractional digits out given so many fractional digits in.
>
>                       regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to [EMAIL PROTECTED] so that your
>        message can get through to the mailing list cleanly

--
Bruce Momjian  <[EMAIL PROTECTED]>          http://momjian.us
EnterpriseDB                               http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq
```