Re: [Nolug] mysql vs postgres

From: Petri Laihonen <pietu_at_weblizards.net>
Date: Mon, 08 Dec 2008 14:39:47 -0600
Message-ID: <493D8613.5010304@weblizards.net>

Ron Johnson wrote:

On 12/08/08 13:56, Petri Laihonen wrote:

Ron,

you asked about which wiki I found the comparison....
It was this: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL


And more specifically in my case:

MySQL supports INSERT IGNORE and REPLACE statements which inserts if a row exists and does nothing otherwise or replaces the current row, respectively.

<Shakes my head in disgust.>

PostgreSQL supports neither of these statements and suggests using stored procedures to get around the lack of these statements. However, there are major shortcomings:

There was some other insert method in mysql addition to the above, but I do not remember it now. But the functionality was such that the row gets updated if it exists, otherwise inserted.

At the worst times, MySQL makes things too sloppily easy for the programmer, and then silents destroys or mangles data.

Three examples from a 10-day old post to ArsTechnica:

http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/377006385931

mysql> create table test (id int auto_increment not null,
       primary key (id)) engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)


Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00');

If ALTER TABLE for an InnoDB table results in changes to column values (for example, because a column is truncated), InnoDB's FOREIGN KEY constraint checks do not notice possible violations caused by changing the values;

Is there an equivalent post for postgres?
I'm sure it exists somewhere :-)

I have not experienced any of the problems mentioned, then on the other hand, I'm not database expert and as a result, will only do basic stuff. I know one can make astronomically complex queries and such, but even then my problem would be to figure out, whether or not the query result is correct. One only needs to change minor thing in the query, and the result values change, not necessarily by much, but they change. Then the question is which really is the correct one...

The reasons for using Mysql seem to be fairly accurate:
I suppose MySQL gets used out of three things:

• It was the default that came with something / only option available on the hosting provider. This is really caused by the following two things.
• People are sick of paying for things like Oracle and run to MySQL because it's free and don't consider anything else
• People just aren't aware of MySQL's gross problems, or just don't understand why they're so bloody important. A lot of these issues are corner cases to some degree, which is why some people don't understand how this stuff is so problematic.

Petri
___________________ Nolug mailing list nolug@nolug.org Received on 12/08/08