FAQ
I'm building a table (which is a report that has to be printed) with a
bunch of items (up to 300 in some cases) that have unitary price
(stored in a numeric(9,2) field), how many there are, and the total
price for each item. At the end of the table there is a total of all
the items.

The app is running on PHP and PG is the backend.

The question is, how do I get the total of everything?

Running it on PHP gives one value, doing a sum() on the backend gives
another, and I'm starting to notice that even using python as a
calculator gives me errors (big ones). Right now I'm doing the maths
by hand to find out who has the biggest error, or if any is 100%
accurate.

Any ideas?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

Search Discussions

  • Merlin Moncure at Jul 18, 2011 at 10:04 pm

    2011/7/18 Martín Marqués <martin.marques@gmail.com>:
    I'm building a table (which is a report that has to be printed) with a
    bunch of items (up to 300 in some cases) that have unitary price
    (stored in a numeric(9,2) field), how many there are, and the total
    price for each item. At the end of the table there is a total of all
    the items.

    The app is running on PHP and PG is the backend.

    The question is, how do I get the total of everything?

    Running it on PHP gives one value, doing a sum() on the backend gives
    another, and I'm starting to notice that even using python as a
    calculator gives me errors (big ones). Right now I'm doing the maths
    by hand to find out who has the biggest error, or if any is 100%
    accurate.
    I wouldn't even bother testing client implementations that are using
    floating point numbers to do the math -- they are going to be wrong.
    If you snoop around you should find math libraries that handle do a
    better job handling exact numbers for most popular languages. A few
    languages, like the hated COBOL, have them built in.

    merlin
  • Craig Ringer at Jul 18, 2011 at 11:54 pm

    On 19/07/2011 6:04 AM, Merlin Moncure wrote:
    I wouldn't even bother testing client implementations that are using
    floating point numbers to do the math -- they are going to be wrong.
    If you snoop around you should find math libraries that handle do a
    better job handling exact numbers for most popular languages.
    You'll also need to convince your database interface to use the decimal
    library you choose, or get it to return 'numeric' types as strings so
    you can convert them with your chosen library yourself.

    It'll do you no good to convert from Pg numeric -> floating point ->
    exact decimal library format.

    --
    Craig Ringer

    POST Newspapers
    276 Onslow Rd, Shenton Park
    Ph: 08 9381 3088 Fax: 08 9388 2258
    ABN: 50 008 917 717
    http://www.postnewspapers.com.au/
  • Chris Travers at Jul 19, 2011 at 12:34 am

    2011/7/18 Merlin Moncure <mmoncure@gmail.com>:
    2011/7/18 Martín Marqués <martin.marques@gmail.com>:
    I'm building a table (which is a report that has to be printed) with a
    bunch of items (up to 300 in some cases) that have unitary price
    (stored in a numeric(9,2) field), how many there are, and the total
    price for each item. At the end of the table there is a total of all
    the items.

    The app is running on PHP and PG is the backend.

    The question is, how do I get the total of everything?

    Running it on PHP gives one value, doing a sum() on the backend gives
    another, and I'm starting to notice that even using python as a
    calculator gives me errors (big ones). Right now I'm doing the maths
    by hand to find out who has the biggest error, or if any is 100%
    accurate.
    I wouldn't even bother testing client implementations that are using
    floating point numbers to do the math -- they are going to be wrong.
    If you snoop around you should find math libraries that handle do a
    better job handling exact numbers for most popular languages.  A few
    languages, like the hated COBOL, have them built in.
    All my testing has suggested that Perl number addition, etc. is
    arbitrary precision safe. (I usually use additional libraries for
    finer control though).

    I don't know about PHP.

    Best Wishes,
    Chris Travers
  • Chris Travers at Jul 19, 2011 at 12:35 am
    BTW, since this is numeric() I would trust the Pg backend before I
    would trust the client languages.

    Best Wishes,
    Chris Travers

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 18, '11 at 9:59p
activeJul 19, '11 at 12:35a
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase