[NTLUG:Discuss] MYSql vs. postgresql

[Paul Ingendorf]

I can't believe I missed the fud express.

Quoting David <david at hayes-family.org>:

> I suppose _someone_ ought to say something nice about PostgreSQL.  
> 
> I've used both, and have now settled on PostgreSQL for my projects.  
> 
> It appears to me that the primary advantage of Mysql is performance;
> it's faster for some types of operations, typically reads, and most
> DB's do a lot more reads than writes.  
> 
> On the other hand, PostgreSQL is a much more complete implementation
> of ANSI SQL.  PG supports:
> 
>  * sub-queries, such as: SELECT * FROM SALES WHERE ITEM_COLOR IS IN
>    (SELECT COLOR FROM ....).  To do this in Mysql, you need to run
>    multiple queries and tie them together in your PHP or Perl script. 

I have always dealt with this using
select * from sales,otherdb where sales.item_color = otherdb.color
(Comming soon)
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

>  * full support for JOINS.  Joins are more than just a cute
>    convenience; they're critical to being able to decompose a database
>    problem into the canonical "normal forms".  Normal forms assure
>    logical consistency in the database, so this can make a huge
>    difference in the quality of your results.

http://www.mysql.com/doc/en/JOIN.html

>  * transactions.   BEGIN ... COMMIT, with full rollback in case of
>    error.  

http://www.mysql.com/doc/en/COMMIT.html

>  * full serializability of operations.  PostgreSQL is ACID-capable.

http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX137

>  * constraints.  the database can be told how items are related, and
>    will enforce those relationships.  For instance, the color of an
>    item must be chosen from one of the values in the COLORS table.
>    Any attempt to insert a different value will be rejected.

http://www.mysql.com/doc/en/SEC455.html


I just had to point this out as I've seen this argument numerous times and just 
a quick search of the mysql manual shows that a lot of it simply isn't true.  I 
know at one time it all was but you have to give props to the mysql developers 
for getting the job done.