The following are the points that I could gather about PostgreSQL vs MySQL(It has been collected from multiple sources, and based on personal experience, please point out any errors or incompleteness that might have inadvertently crept in):
- WITH queries, also know as Common table expression(CTE) is not available in MySQL, is available in PostgreSQL.
(It has been requested from 2006 onwards: http://bugs.mysql.com/bug.php?id=16244)
More Details: http://stackoverflow.com/a/325243 - PostgreSQL offers more enterprise DB features, provides PL/SQL support.
- PostgreSQL offers a more detailed and low-level query EXPLAIN result, along with the execution plan, if required. In comparison MySQL EXPLAIN/DESCRIBE results are more limited.
- PostgreSQL has only one DB engine, whereas MySQL has many like INNODB, MyISAM, etc..
- MySQL is natively supported in cPanel(widely used server admin panel), for PostgreSQL it has to be installed manually, and support is partial,
so its easy to find a hosting provider with MySQL support, when compared to PostgreSQL. - PostgreSQL follows proper SQL standards compliance, but MySQL doesn’t, so integration with other RDBMSs might cause problems.
- PostgreSQL supports multiple schemas per database, in MySQL, a datbase and schema can be considered the same, for all intents and purposes.
- PostgreSQL supports INTERSECT, EXCEPT ( set operations), MySQL does not.
https://en.wikipedia.org/wiki/Set_operations - PostgrSQL supports merge joins( also calles Sort merge joins), MySQL does not
http://use-the-index-luke.com/sql/join/sort-merge-join - PostgreSQL supports more types of index, like Inverted index, GiST(used in postGIS), bitmap index, reverse index, partial index.
For more details, check out:
Ramblings of an eternally curious guy 