PostgreSQL vs Mysql comparison

Hi!

Scott> I have pasted the full text below relative to MYSQL AND POSTGRESQL
Scott> let's try to use this as a working document and update it.
Scott> Thanks, Scott

Some comments:

Scott> Low-Cost Unix Database Differences   - Author Unknown

Scott> MySQL:
Scott> -------
Scott> Pros:
Scott> * Extremely fast.
Scott> * Regular expression support.
Scott> * Quite a few datatypes available.
Scott> * Support staff very knowledgable and helpful.
Scott> * Supports identity/auto-increment columns, similar to sequences.
Scott> * C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
Scott> shell script is included that does the basic port ( via sed )
Scott> * CHAR/VARCHAR/TEXT columns handled canse insensetively.
Scott> * Low-cost, often free, for most users. Commercial support available.
Scott> * BLOB support stores the BLOBs in the table.
Scott> * Supports user-defined functions in C and derivatives.
Scott> * Multithreaded

Scott> CONS:

Scott> * No support for transactions ( begin transaction, rollback, commit ). One must
Scott> explicitly lock a table and unlock it when finished.
Scott> * No foreign key support.
Scott> * No trigger support.
Scott> * No subselects.
Scott> * No views.
Scott> * No sequence support. No, auto_increment columns are not sequences.

Note that sequences is not defined by ANSI SQL;  AUTO_INCREMENT colums
are about as much standard than sequences.
Note that you can VERY easily emulate sequences in MySQL

Scott> * Auto_increment columns will give the next available number upon insert,
Scott> possibly messing up what little referential integrity constraints your
Scott> application attempts to enforce.

If you write your application properly, it's note a problem even if one
reuse numbers that is not in use.  In MySQL 3.23 you will also always get a
new auto_increment number on insert.

Scott> * Only one automatically-updated column per table; one cannot have a timestamp
Scott> and auto_increment column in the same table and have both be updated.

The above is not true. There has never been a problem with having a
timestamp and a auto_increment column in the same table.

Scott> * User-defined functions, even the most basic ones, must be in C and
Scott> derivatives.
Scott> * No on-line recovery; one must bring down the database server and run the
Scott> 'isamchk' utility on their datafiles.

OPTIMIZE table should be comparable to VACUM on postgreSQL.

Scott> * Hacked-up SQL functions such as REPLACE INTO.

Scott> * Indexes must be created at table-creation time. To add a new index/key, the
Scott> table (and its data) must be backed up and reimported. Work around: create a new
Scott> table, insert into new_table select * from old_table, drop old_table, alter
Scott> table new_table rename old_table.

The above was fixed 2 1/2 years ago.

Scott> * Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
Scott> datetime column, mysql accepts it.

We regard this as a feature, not as a bug.  It's not the databases job
to validate data by default, only to store and retrieve it.

Note that MySQL 3.23 allows you to use 'fuzzy' dates: '1999-02-00'.
This is VERY useful when you store birth-days/death-days!

Scott> * Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
Scott> == '1992'

This is because the above means:

select (convert to int) '1999-08-14 11:32:00' - 7

->

select 1999 - 7.

Because '1999-08-14 11:32:00' is not a date object, but a string.

You should instead use:

SELECT DATE_SUB('1999-08-14 11:32:00',INTERVAL 7 DAYS)

or even

SELECT '1999-08-14 11:32:00' - INTERVAL 7 DAYS

Regards,
Monty