Oh, I agree. PostgreSQL is a much more well-behaved RDBMS than MySQL ever was. I'm more inclined to select PostgreSQL over MySQL, but I may not be able to convince management that it's a better choice no matter how technically superior I can show it to be. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: Scott Marlowe [mailto:smarlowe@xxxxxxxxxxxxxxxxx] Sent: Monday, August 28, 2006 2:21 PM To: Brandon Aiken Cc: pgsql general Subject: Re: [GENERAL] Precision of data types and functions On Mon, 2006-08-28 at 12:28, Brandon Aiken wrote: > I'm considering migrating our MySQL 4.1 database (barf!) to PostgreSQL 8 > or MySQL 5. > > The guy who originally designed the system made all the number data > FLOATs, even for currency items. Unsurprisingly, we've noticed math > errors resulting from some of the aggregate functions. I've learned > MySQL 5 stores numbers with the DECIMAL data type as text strings, and > does math at 64-bit precision. Where can I find information about how > precise PostgreSQL 8 math is? Much the same. I'll let the other poster's reference to numeric types stand on it's own. Here's why I'd choose PostgreSQL over MySQL: smarlowe@state:~> mysql test mysql> select version(); +-----------------+ | version() | +-----------------+ | 5.0.19-standard | +-----------------+ 1 row in set (0.00 sec) mysql> create table test (a numeric(10,2)); Query OK, 0 rows affected (0.05 sec) mysql> insert into test values (123123123123123.2); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from test; +-------------+ | a | +-------------+ | 99999999.99 | +-------------+ 1 row in set (0.00 sec) ------------------------------------------------------------- psql test test=> select version(); version ------------------------------------------------------------------------ ---------------------------------- PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7) test=> create table test (a numeric(12,2)); CREATE TABLE test=> insert into test values (123123123123123.2); ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^14 for field with precision 12, scale 2. test=> select * from test; a --- (0 rows) I don't trust a database that inserts something other than I told it to insert and only gives me a warning. For more info, take a look at these two pages and compare: http://sql-info.de/mysql/gotchas.html http://sql-info.de/postgresql/postgres-gotchas.html
