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

Thank you, that is exatly why I love the postgres database so much!!

I write all my programs to the tune of they either work or die and complain
loudly :)

Josh.

On March 24, 2003 04:46 pm, scott.marlowe wrote:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)