PostgreSQL vs MySQL (was Re: How to handle sockets - easily?)

[J Peyret]

On Mar 16, 10:19 am, a... at pythoncraft.com (Aahz) wrote:
> In article <fdjt28-flo.... at wilbur.25thandClement.com>,

> >always recommend people to use PostgreSQL, though; which is superior in
> >almost every way, especially the C client library and the wire protocol.)
>
> Can you point at a reference for the latter?  I have been trying to
> convince my company that PG is better than MySQL.
> --

Well, my $.02 worth is that, about 3 yrs ago, on 5.0x-5.1x, I was
truly appalled by the sheer level of stupidity displayed by MySQL's
handling of a moderately complex UPDATE SQL query with a correlated
subquery.

(Let's say this was a 7 out of 10 complexity, with your standard
selects being 1-3 max and a nightmare update query with all sorts of
correlated subqueries would be a 9.  I am first of all a database
programmer, so queries are my world).

Not only did MySQL mangle the query because it didn't understand what
I was asking, it thrashed the data during the update and committed
it.  And, when I reviewed the query once again, I found I had
mismatched parentheses, so it wasn't even syntaxically correct.  Truly
scary.

DB2 + SQLBase punted for years on correlated subqueries  Ex:  "update
ORDERS where x=y and exists (select 1 from ORDERS where <some
condition>)".  The DB2 engine doesn't know how to handle them, so it
tells you to get lost.

MySQL is not smart enough to recognize it's over its head and instead
makes a best effort.  To me it looks like a database that will get you
80% there and steadfastly refuse the last 20%, assuming you need
really clever queries.  PG was much cleaner in behavior, though a pain
to install, especially on Windows.