Re: Question about the TODO, numerics, and division

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Travers <chris(at)verkiel(dot)metatrontech(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Question about the TODO, numerics, and division
Date: 2007-03-21 00:27:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Chris Travers <chris(at)verkiel(dot)metatrontech(dot)com> 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)) ;
(1 row)

and of course if you multiply that by 6 you get

regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;
(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;
(1 row)


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

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)) ;
(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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2007-03-21 00:38:24 Re: Patch for pg_dump
Previous Message Tom Lane 2007-03-21 00:03:05 Re: Fixing hash index build time