Skip site navigation (1) Skip section navigation (2)

Re: BIG files

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Bruno Wolff III <bruno(at)wolff(dot)to>, rabt(at)dim(dot)uchile(dot)cl,pgsql-novice(at)postgresql(dot)org
Subject: Re: BIG files
Date: 2005-06-19 14:17:42
Message-ID: 20050619141742.GA18645@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, Jun 19, 2005 at 07:48:08AM -0500, Bruno Wolff III wrote:
> 
> From discussions I have seen here, MYSQL implements Numeric using a floating
> point type. Postgres stores it using something like a base 10000 digit
> for each 4 bytes of storage.

Hmmm...that got me wondering about something....

CREATE TABLE foo (n1 decimal(17, 0), n2 decimal(17, 0));
INSERT INTO foo VALUES (10000000000000000, 10000000000000001);
SELECT n1, n2, n1 + n2 FROM foo;

PostgreSQL 8.0.3:

        n1         |        n2         |     ?column?      
-------------------+-------------------+-------------------
 10000000000000000 | 10000000000000001 | 20000000000000001

MySQL 4.1.12:

+-------------------+-------------------+-------------------+
| n1                | n2                | n1 + n2           |
+-------------------+-------------------+-------------------+
| 10000000000000000 | 10000000000000001 | 20000000000000000 |
+-------------------+-------------------+-------------------+

Wrong sum in MySQL.  Nice.  Oh wait, even better....

CREATE TABLE foo (n1 decimal(20, 0), n2 decimal(20, 0));
INSERT INTO foo VALUES (10000000000000000000, 10000000000000000001);
SELECT n1, n2, n1 + n2 FROM foo;

PostgreSQL 8.0.3:

          n1          |          n2          |       ?column?       
----------------------+----------------------+----------------------
 10000000000000000000 | 10000000000000000001 | 20000000000000000001

MySQL 4.1.12:

+----------------------+----------------------+-----------------------+
| n1                   | n2                   | n1 + n2               |
+----------------------+----------------------+-----------------------+
| -8446744073709551616 | -8446744073709551615 | -16893488147419099136 |
+----------------------+----------------------+-----------------------+

Beauty.  Nice of MySQL not to raise an error if I've exceeded some
implementation-defined limit.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2005-06-19 16:33:01
Subject: Re: BIG files
Previous:From: Bruno Wolff IIIDate: 2005-06-19 12:48:08
Subject: Re: BIG files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group