InnoDB engine is fully ACID compliant, but using InnoDB in MySQL fails ACIDity, because MySQL doesn't propagate the triggers of foreign keys for this engine. InnoDB is a storage engine, it does not execute the queries. Until MySQL is ACID compliant, outside of the storage engines, any storage engine running on it will not conform.

Some other MySQL engines are said to be ACID compliant, like Falcon / Cluster.

PostgreSQL is acknowledged as having a more rigorous approach to robustness and data integrity.

PostgreSQL and MySQL both have an impressive array of features that increase data integrity, functionality, and performance. The features included in a database may help improve performance, ease of use, functionality, or stability.

A "gotcha" is a feature or function which works as advertised - but not as expected.

—

PostgreSQL supporters claim that MySQL has more "gotchas"[3] than PostgreSQL[4], due to its deviation from the SQL standard, and its various functional limitations which may not seem intuitively obvious to a new user. While retaining backwards compatibility, MySQL has introduced various SQL modes that increase standards compliance and produce results that may be more expected by those unfamiliar with MySQL.

In certain aspects, such as case sensitivity in CHAR fields, MySQL's

behaviour [...] is the diametrical opposite of the default behaviour of most other databases. (Tested: DB2 8.1, Firebird 1.5.1, Oracle 8.1.7 and PostgreSQL 7.4.3 [...])

—

MySQL supports the statements, 'INSERT IGNORE', which inserts if a row doesn't exist, and 'REPLACE', which replaces the current row.

Currently PostgreSQL supports neither of these statements and suggests using stored procedures to get around the lack of these statements. However, there are major shortcomings:

it can only insert a single value at a time. This is a major performance limitation, and also suffers concurrency issues. INSERT IGNORE and REPLACE handle multi-valued inserted much more gracefully.

— Robin Johnson ,  PostgreSQL Vs. MySQL for INSERT IGNORE + REPLACE - stored procedures, savepoints and beyond

When appropriate, PostgreSQL can also use RULEs to produce any of these behaviors on a normal INSERT or UPDATE; this can also be used on a view.

A similar MySQL feature INSERT ... ON DUPLICATE UPDATE is also missing from PostgreSQL and requires use of stored procedures (which can work only on one row at a time) or RULEs, which are not so constrained.

PostgreSQL will feature the MERGE clause in a future version [5], which follows the SQL:2008 standard. This can do the same as MySQL's non-standard 'INSERT IGNORE','REPLACE' and 'INSERT ... ON DUPLICATE UPDATE' statements but with more granularity.

Both PostgreSQL and MySQL support Not-Null, Unique, and Primary Key constraints. Foreign Key constraints are supported by PostgreSQL and by MySQL's InnoDB storage engine, but not other engines. However MySQL silently ignores the CHECK constraint which PostgreSQL has supported for a long time.

InnoDB tables support checking of foreign key constraints... For other storage engines, MySQL Server parses and silently ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements.

—MySQL AB , MySQL 5.1 Reference Manual :: 12.1.9. CREATE TABLE Syntax

For MySQL engines not supporting foreign key constraints or to implement constraints between tables in differing engines, can be used to check the constraint, although this is not a guarantee as it's prone to race conditions. [citation needed]

Also, the support of ForeignKeys in MySQL is not yet complete, as cascade actions are not considered as SQL statements and thus do not fire triggers etc. (they only cascade further)

PostgreSQL also supports deferrable constraints as specified in the SQL standard, allowing individual constraints to be temporarily violated locally inside a transaction, with validation deferred until commit time. Transaction isolation and atomicity (ACID) guarantees that the constraint-violating data is never visible to other users. Currently only Unique, Primary Key and Foreign Key constraints are deferrable, but deferrable Not-Null and Check constraints can be emulated using deferrable constraint triggers. MySQL does not support deferrable constraints.

PostgreSQL allows any function (whether marked as IMMUTABLE, STABLE or VOLATILE) to be used as the default value for a column. Currently, NOW() is the only function that can be used as a default value in a MySQL table -- and can only be applied to one column per table, on TIMESTAMP columns only.

MySQL supports stored procedures, per se; PostgreSQL supports , which are in practice very similar.