Thread: Precision of data types and functions
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? -- Brandon Aiken CS/IT Systems Engineer
"Brandon Aiken" <BAiken@winemantech.com> writes: > 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? You probably want to use NUMERIC for currency values. See: http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-NUMERIC-DECIMAL -Doug
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
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@g2switchworks.com]
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
Scott Marlowe <smarlowe@g2switchworks.com> writes: > 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. Uhm 10^14? What version of Postgres is that?! Though I get a correct text with 8.2CVS: postgres=> insert into test values (123123123123123.2); ERROR: numeric field overflow DETAIL: A field with precision 12, scale 2 must have an absolute value less than 10^10. I guess Tom fixed some bugs when he reimplemented NUMERIC a while back. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Mon, 2006-08-28 at 13:54, Gregory Stark wrote: > Scott Marlowe <smarlowe@g2switchworks.com> writes: > > > 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. > > Uhm 10^14? What version of Postgres is that?! It was in the post, it's 7.4.12 (just upgraded it to 7.4.13, as it was my workstation) It's the version we're stuck on for a while here work. 10^14 is just another way of saying precision 12, or somewhat close to it. My point being that a two+ year old version of PostgreSQL is still superior to the latest release version of MySQL...
"Brandon Aiken" <BAiken@winemantech.com> writes: > 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. Just show them how much money they might loose with a simple bug as the one that was shown to you on the last post :-) Money speaks very loud for any manager... -- Jorge Godoy <jgodoy@gmail.com>
To be fair, that's the fault of the previous designer, not MySQL. You don't blame Stanley when your contractor uses 2" plain nails when he needed 3" galvanized. The tool isn't to blame just because someone used it incorrectly. MySQL works great for what it does: high speed at a cost of data integrity. It's fine for discussion boards or anything non-critical where having a database is a convenience instead of a necessity. Nevermind that MySQL really doesn't have much place between PostgreSQL and SQLite nowadays. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: Jorge Godoy [mailto:jgodoy@gmail.com] Sent: Monday, August 28, 2006 3:36 PM To: Brandon Aiken Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Precision of data types and functions "Brandon Aiken" <BAiken@winemantech.com> writes: > 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. Just show them how much money they might loose with a simple bug as the one that was shown to you on the last post :-) Money speaks very loud for any manager... -- Jorge Godoy <jgodoy@gmail.com>
Gregory Stark <gsstark@mit.edu> writes:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
>> 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.
> Uhm 10^14? What version of Postgres is that?!
Looks like any current release :-(
> Though I get a correct text with 8.2CVS:
> ...
> I guess Tom fixed some bugs when he reimplemented NUMERIC a while back.
No, I think Bruce fixed this recently. It's just a cosmetic mistake in
the error message so we didn't back-patch it.
regards, tom lane
I wrote:
> No, I think Bruce fixed this recently. It's just a cosmetic mistake in
> the error message so we didn't back-patch it.
No, strike that, I remember the discussion now. The pre-8.2 code is
correct on its own terms, which is that it's telling you what size
number you tried to put in:
regression=# select '123123123123123.2'::numeric(12,2);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^14 for field with precision 12, scale 2.
regression=# select '12123123123123123.2'::numeric(12,2);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^16 for field with precision 12, scale 2.
regression=#
Bruce and some other people thought this was confusing, so it's been
changed for 8.2.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Bruce and some other people thought this was confusing, so it's been > changed for 8.2. No kidding. They confused me. Well Thanks for the explanation, The new messages are infinitely clearer -- greg
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Brandon Aiken wrote: > To be fair, that's the fault of the previous designer, not MySQL. > You don't blame Stanley when your contractor uses 2" plain nails > when he needed 3" galvanized. The tool isn't to blame just > because someone used it incorrectly. Shows that you've been afflicted with the MySQL "app developer must do everything" disease. Just as a PK should not let you insert a duplicate record, a NUMERIC(12,2) should not let you insert a too-big number. Tool analogy: Pneumatic nailer says "maximum nail length 3 inches", but it *lets* you install *4* inch nails. So, you do what you can, it mis-fires and you nail your hand to the deck. Who's fault is it? Theirs, for making it easy to install 4 inch nails, or yours for doing it? That's where the analogy breaks down. DBMSs have *always* returned errors when the app tries to do something beyond the range of the DB's parameters. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE823ES9HxQb37XmcRAi2bAKDXSW7ImqWSmpYKLGKFUxkdxtdz/QCgt2RM DiTn9wpUZoOJ8WIrFXxKmQ4= =U6SP -----END PGP SIGNATURE-----
Oh, I'm not saying that MySQL is a full-featured database, nor saying that I agree with the MySQL philosophy. I don't. That's why I'm trying to avoid MySQL. However PostgreSQL isn't any more accurate with FLOATs than MySQL is. The ANSI SQL standard for FLOAT is for an inaccurate number. It was never meant to be accurate, so even though MySQL has a much more liberal philosophy it's still behaving correctly when it does the math inaccurately. Which is just like I would expect PostgreSQL or DB2 or Oracle to do. If you need numeric accuracy and you pick FLOAT for your field, that *is* the developer's fault. You picked a screwdriver when you needed a chisel. Now, MySQL's design to 9-fill fields when you try to enter a too-large number is, in fact, stupid on MySQL's part. I consider that silent truncation. Heck, MySQL lets you create a date on February 31st, or prior to the year 1500, both of which are obviously nonsensical. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson Sent: Monday, August 28, 2006 6:27 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Precision of data types and functions -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Brandon Aiken wrote: > To be fair, that's the fault of the previous designer, not MySQL. > You don't blame Stanley when your contractor uses 2" plain nails > when he needed 3" galvanized. The tool isn't to blame just > because someone used it incorrectly. Shows that you've been afflicted with the MySQL "app developer must do everything" disease. Just as a PK should not let you insert a duplicate record, a NUMERIC(12,2) should not let you insert a too-big number. Tool analogy: Pneumatic nailer says "maximum nail length 3 inches", but it *lets* you install *4* inch nails. So, you do what you can, it mis-fires and you nail your hand to the deck. Who's fault is it? Theirs, for making it easy to install 4 inch nails, or yours for doing it? That's where the analogy breaks down. DBMSs have *always* returned errors when the app tries to do something beyond the range of the DB's parameters. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE823ES9HxQb37XmcRAi2bAKDXSW7ImqWSmpYKLGKFUxkdxtdz/QCgt2RM DiTn9wpUZoOJ8WIrFXxKmQ4= =U6SP -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
On Fri, 2006-09-01 at 10:37, Brandon Aiken wrote: > Oh, I'm not saying that MySQL is a full-featured database, nor saying > that I agree with the MySQL philosophy. I don't. That's why I'm trying > to avoid MySQL. > > However PostgreSQL isn't any more accurate with FLOATs than MySQL is. > The ANSI SQL standard for FLOAT is for an inaccurate number. It was > never meant to be accurate, so even though MySQL has a much more liberal > philosophy it's still behaving correctly when it does the math > inaccurately. Which is just like I would expect PostgreSQL or DB2 or > Oracle to do. If you need numeric accuracy and you pick FLOAT for your > field, that *is* the developer's fault. You picked a screwdriver when > you needed a chisel. > > Now, MySQL's design to 9-fill fields when you try to enter a too-large > number is, in fact, stupid on MySQL's part. I consider that silent > truncation. Heck, MySQL lets you create a date on February 31st, or > prior to the year 1500, both of which are obviously nonsensical. What's nonsensical about a date before the year 1500??? it's not like that didn't exist or something.
The Gregorian calendar was established in the 1500's by Pope Gregory, so, no, those dates did not exist. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: Scott Marlowe [mailto:smarlowe@g2switchworks.com] Sent: Friday, September 01, 2006 2:22 PM To: Brandon Aiken Cc: pgsql general Subject: Re: [GENERAL] Precision of data types and functions On Fri, 2006-09-01 at 10:37, Brandon Aiken wrote: > Oh, I'm not saying that MySQL is a full-featured database, nor saying > that I agree with the MySQL philosophy. I don't. That's why I'm trying > to avoid MySQL. > > However PostgreSQL isn't any more accurate with FLOATs than MySQL is. > The ANSI SQL standard for FLOAT is for an inaccurate number. It was > never meant to be accurate, so even though MySQL has a much more liberal > philosophy it's still behaving correctly when it does the math > inaccurately. Which is just like I would expect PostgreSQL or DB2 or > Oracle to do. If you need numeric accuracy and you pick FLOAT for your > field, that *is* the developer's fault. You picked a screwdriver when > you needed a chisel. > > Now, MySQL's design to 9-fill fields when you try to enter a too-large > number is, in fact, stupid on MySQL's part. I consider that silent > truncation. Heck, MySQL lets you create a date on February 31st, or > prior to the year 1500, both of which are obviously nonsensical. What's nonsensical about a date before the year 1500??? it's not like that didn't exist or something.
On Fri, 2006-09-01 at 13:24, Brandon Aiken wrote:
> > Now, MySQL's design to 9-fill fields when you try to enter a too-large
> > number is, in fact, stupid on MySQL's part. I consider that silent
> > truncation. Heck, MySQL lets you create a date on February 31st, or
> > prior to the year 1500, both of which are obviously nonsensical.
>
> What's nonsensical about a date before the year 1500??? it's not like
> that didn't exist or something.
> The Gregorian calendar was established in the 1500's by Pope Gregory,
> so, no, those dates did not exist.
That makes no sense whatsoever. Just because the calendar is a somewhat
modern invention doesn't mean that the year 45 BC doesn't exist... How
else are we to keep track of dates from before that time? Switch to the
Mayan calendar? I'm pretty sure no one's made a Mayan Calendar
extension for PostgreSQL (or any other database) just yet.
test=> insert into test values ('1023-04-12 BC');
INSERT 2124397005 1
test=> insert into test values ('1023-04-12');
INSERT 2124397005 1
test=> select * from test;
t
------------------------
1023-04-12 00:00:00
1023-04-12 00:00:00 BC
(2 rows)
Of course the year exists. The date itself is nonsensical, however.
'January 3, 648' does reference a valid day, but the date itself has no
meaning at that time in the world, so there is very little meaning in
using Gregorian dates except to give us a relativistic idea of when it
occurred. Nevertheless, you can never say with specificity anything
that occurred on any given date prior to the inception of the Gregorian
calendar without doing conversions to a calendar no longer in use while
taking into account the numerous error corrections that have been made
to various calendars. Knowing the year and season something happened is
about the best that can be expected.
--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Friday, September 01, 2006 2:27 PM
To: Brandon Aiken
Cc: pgsql general
Subject: RE: [GENERAL] Precision of data types and functions
On Fri, 2006-09-01 at 13:24, Brandon Aiken wrote:
> > Now, MySQL's design to 9-fill fields when you try to enter a
too-large
> > number is, in fact, stupid on MySQL's part. I consider that silent
> > truncation. Heck, MySQL lets you create a date on February 31st, or
> > prior to the year 1500, both of which are obviously nonsensical.
>
> What's nonsensical about a date before the year 1500??? it's not like
> that didn't exist or something.
> The Gregorian calendar was established in the 1500's by Pope Gregory,
> so, no, those dates did not exist.
That makes no sense whatsoever. Just because the calendar is a somewhat
modern invention doesn't mean that the year 45 BC doesn't exist... How
else are we to keep track of dates from before that time? Switch to the
Mayan calendar? I'm pretty sure no one's made a Mayan Calendar
extension for PostgreSQL (or any other database) just yet.
test=> insert into test values ('1023-04-12 BC');
INSERT 2124397005 1
test=> insert into test values ('1023-04-12');
INSERT 2124397005 1
test=> select * from test;
t
------------------------
1023-04-12 00:00:00
1023-04-12 00:00:00 BC
(2 rows)
On Fri, 2006-09-01 at 13:33, Brandon Aiken wrote:
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
> Sent: Friday, September 01, 2006 2:27 PM
> To: Brandon Aiken
> Cc: pgsql general
> Subject: RE: [GENERAL] Precision of data types and functions
>
> On Fri, 2006-09-01 at 13:24, Brandon Aiken wrote:
> > > Now, MySQL's design to 9-fill fields when you try to enter a
> too-large
> > > number is, in fact, stupid on MySQL's part. I consider that silent
> > > truncation. Heck, MySQL lets you create a date on February 31st, or
> > > prior to the year 1500, both of which are obviously nonsensical.
> >
> > What's nonsensical about a date before the year 1500??? it's not like
> > that didn't exist or something.
> > The Gregorian calendar was established in the 1500's by Pope Gregory,
> > so, no, those dates did not exist.
>
> That makes no sense whatsoever. Just because the calendar is a somewhat
> modern invention doesn't mean that the year 45 BC doesn't exist... How
> else are we to keep track of dates from before that time? Switch to the
> Mayan calendar? I'm pretty sure no one's made a Mayan Calendar
> extension for PostgreSQL (or any other database) just yet.
>
> test=> insert into test values ('1023-04-12 BC');
> INSERT 2124397005 1
> test=> insert into test values ('1023-04-12');
> INSERT 2124397005 1
> test=> select * from test;
> t
> ------------------------
> 1023-04-12 00:00:00
> 1023-04-12 00:00:00 BC
> (2 rows)
> Of course the year exists. The date itself is nonsensical, however.
>
> 'January 3, 648' does reference a valid day, but the date itself has no
> meaning at that time in the world, so there is very little meaning in
> using Gregorian dates except to give us a relativistic idea of when it
> occurred. Nevertheless, you can never say with specificity anything
> that occurred on any given date prior to the inception of the Gregorian
> calendar without doing conversions to a calendar no longer in use while
> taking into account the numerous error corrections that have been made
> to various calendars. Knowing the year and season something happened is
> about the best that can be expected.
Never the less, you can input dates before 1500 in PostgreSQL and almost
any other database. It's not a failing of MySQL nor PostgreSQL that
they can accept dates before 1500.
On Fri, 2006-09-01 at 13:33, Brandon Aiken wrote: > Of course the year exists. The date itself is nonsensical, however. > > 'January 3, 648' does reference a valid day, but the date itself has no > meaning at that time in the world, so there is very little meaning in > using Gregorian dates except to give us a relativistic idea of when it > occurred. Nevertheless, you can never say with specificity anything > that occurred on any given date prior to the inception of the Gregorian > calendar without doing conversions to a calendar no longer in use while > taking into account the numerous error corrections that have been made > to various calendars. Knowing the year and season something happened is > about the best that can be expected. Actually, it's far worse than that. I was reading up on the adoption rate of the gregorian calendar, and because of delays in its adoption by various folks over the centuries since it was invented (and the fact that some religions STILL don't recognize it) there are many dates that are recorded differently throughout history since it was created. I read up a bit on it at http://en.wikipedia.org/wiki/Gregorian_calendar and it's enough to make your head spin... The trivia section near the bottom is particularly interesting.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Brandon Aiken wrote: > Oh, I'm not saying that MySQL is a full-featured database, nor saying > that I agree with the MySQL philosophy. I don't. That's why I'm trying > to avoid MySQL. > > However PostgreSQL isn't any more accurate with FLOATs than MySQL is. > The ANSI SQL standard for FLOAT is for an inaccurate number. It was > never meant to be accurate, so even though MySQL has a much more liberal > philosophy it's still behaving correctly when it does the math > inaccurately. Which is just like I would expect PostgreSQL or DB2 or > Oracle to do. If you need numeric accuracy and you pick FLOAT for your > field, that *is* the developer's fault. You picked a screwdriver when > you needed a chisel. I don't think that any of us are criticizing MySQL for it treats floats, since we all (well, the dev team and most DBAs/developers) understand the pitfalls of real numbers. That has *nothing* to do, though, with the bug in question: 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) > Now, MySQL's design to 9-fill fields when you try to enter a too-large > number is, in fact, stupid on MySQL's part. I consider that silent > truncation. Heck, MySQL lets you create a date on February 31st, or > prior to the year 1500, both of which are obviously nonsensical. > > -- > Brandon Aiken > CS/IT Systems Engineer > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson > Sent: Monday, August 28, 2006 6:27 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Precision of data types and functions > > Brandon Aiken wrote: >>> To be fair, that's the fault of the previous designer, not MySQL. >>> You don't blame Stanley when your contractor uses 2" plain nails >>> when he needed 3" galvanized. The tool isn't to blame just >>> because someone used it incorrectly. > > Shows that you've been afflicted with the MySQL "app developer must > do everything" disease. > > Just as a PK should not let you insert a duplicate record, a > NUMERIC(12,2) should not let you insert a too-big number. > > Tool analogy: Pneumatic nailer says "maximum nail length 3 inches", > but it *lets* you install *4* inch nails. So, you do what you can, > it mis-fires and you nail your hand to the deck. Who's fault is it? > Theirs, for making it easy to install 4 inch nails, or yours for > doing it? > > That's where the analogy breaks down. DBMSs have *always* returned > errors when the app tries to do something beyond the range of the > DB's parameters. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE+RxpS9HxQb37XmcRApgwAJ9SoDyxTIDJqNKMVwD2nIsix2E4UwCfaIDf FVfyZ5VXf/0r6VXCC8QSDbM= =g3E8 -----END PGP SIGNATURE-----