From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|

To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |

Cc: | Chris Travers <chris(at)verkiel(dot)metatrontech(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |

Subject: | Re: Question about the TODO, numerics, and division |

Date: | 2007-03-21 21:21:06 |

Message-ID: | 200703212121.l2LLL6p01137@momjian.us |

Views: | Raw Message | Whole Thread | Download mbox | Resend email |

Thread: | |

Lists: | pgsql-hackers |

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

> ?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 majordomo(at)postgresql(dot)org so that your

> message can get through to the mailing list cleanly

--

Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us

EnterpriseDB http://www.enterprisedb.com

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

- Re: Question about the TODO, numerics, and division at 2007-03-21 00:27:46 from Tom Lane

From | Date | Subject | |
---|---|---|---|

Next Message | Bruce Momjian | 2007-03-21 21:25:36 | Re: Fixing hash index build time |

Previous Message | Heikki Linnakangas | 2007-03-21 20:40:05 | Re: relation 71478240 deleted while still in use on 8.1 |