Re: fairly current mysql v postgresql comparison need for - Mailing list pgsql-general

OK, I've thought about it a bit, and have a more considered opinion.  I've
used MySQL a bit, and Postgresql a lot.  I've found that Postgresql and
MySQL seem to have divergent philosophies in many areas.

One of them is value checking of input data.
This next paragraph clearly spells out the philosophy of data checking
being something in the realm of what your program should be doing, so
MySQL doesn't have to.

From their documentation at: http://www.mysql.com/doc/en/Open_bugs.html

** QUOTE **

# Because MySQL Server allows you to work with table types that don't
support transactions, and thus can't rollback data, some things behave a
little differently in MySQL Server than in other SQL servers. This is just
to ensure that MySQL Server never needs to do a rollback for a SQL
command. This may be a little awkward at times as column values must be
checked in the application, but this will actually give you a nice speed
increase as it allows MySQL Server to do some optimisations that otherwise
would be very hard to do. If you set a column to an incorrect value, MySQL
Server will, instead of doing a rollback, store the best possible value in
the column:

** END QUOTE **

NULLS:

Postgresql is pedantic about getting the data right, all right, and
completely right, or throwing up its hands and telling you to get your
data straight and try again.  For instance, if you try to put a null in a
not null column Postgresql will generate an error and not take the data
in.

** QUOTE **

# If you try to store NULL into a column that doesn't take NULL values,
MySQL Server will store 0 or '' (empty string) in it instead. (This
behaviour can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS
compile option.)

** END QUOTE **

DATES:

If you set a wrong or impossible date in postgresql it complains.  If you
don't supply one, and there is no default, it inserts NULL.  If the column
is NOT NULL, it refuses to insert the data.

** QUOTE **

# MySQL allows you to store some wrong date values into DATE and DATETIME
columns (like 2000-02-31 or 2000-02-00). The idea is that it's not the SQL
server job to validate date. If MySQL can store a date and retrieve
exactly the same date, then MySQL will store the date. If the date is
totally wrong (outside the server's ability to store it), then the special
date value 0000-00-00 will be stored in the column.

** END QUOTE **

NUMERIC:

If you try to insert a number outside the range allowed by a numeric,
postgresql will error out.

** QUOTE **

* If you try to store a value outside the range in a numerical column,
MySQL Server will instead store the smallest or biggest possible value in
the column.

** END QUOTE**

Just this one area alone shows the great difference in philosophy between
the two databases.  It's not that one is better than the other, it's that
they're aimed at difference audiences.

If you've got to implement a financial system in a database, and you're
familiar with the commercial databases, then Postgresql seems like
familiar ground.

If you've got to implement a content management system for 28 people who
will be checking out and in documents all day, with locking being the
course grained "checked in / checked out" settings for a file, along with
a timeout and a name of the checker outer, then MySQL is a fine fit.

I've found that this difference makes it a little harder to get started in
Postgresql than MySQL, since Postgresql will spit up on the programmer in
a heart beat if they don't give it the right data.  Data that would slide
right into a MySQL database will get tossed by Postgresql.

I know someone who lost all the dates in a MySQL table because it
never complained when inputting 0000-00-00 when his field format was
wrong and he converted the field from one type to another.  Table
converted, original data gone.  Postgresql doesn't even try to change the
type of a field, since that's an inherently unsafe and uncertain thing to
do, you get to figure it out for yourself in Postgresql.  :-)

MySQL is often run as root.  Not a safe thing for a database program.
Postgresql refuses to run as root.  Every year or so someone comes on the
list opining for the postmaster to be able to run as root, how we don't
trust the users and all.  It's not about that, it's just good safety
practice.

The cornerstone of MySQL is performance above all, the cornerstone of
Postgresql is correctness above all.