PostgreSQL 9.0.0, released today, contains the MySQL Emulation Layer.
To enable this feature, set the mysql_compatible option GUC to "on".
postgres=# SELECT * FROM pg_settings WHERE name = 'mysql_compatible';
-[ RECORD 1 ]----------------------------------------------------------------
name | mysql_compatible
setting | ON
category | Version AND Platform Compatibility / Other Platforms AND Clients
short_desc | Enable MySQL Emulation Layer
context | backend
vartype | bool
SOURCE | DEFAULT
Enabling this option changes the following characteristics (in the entire PostgreSQL cluster):
- The boolean data type now takes positive integers in addition to "true" and "false".
- The default TCP port is 3306 to make porting applications easier.
- Non-aggregate columns in the SELECT list need no longer appear in the GROUP BY clause.
- CAST may or may not work.
- ENUMs can no longer contain numbers, only characters are allowed
- CHAR, VARCHAR and TEXT are case insensitive now. Case sensitive matching is only done with the new BINARY keyword.
- VARCHAR supports texts up to a length of 255 bytes. Bear in mind that since Unicode/UTF8 characters may need more than one byte for any given character, you must take this into account when calculating what VARCHAR can actually store.
- Double dash comments must be start with a space after the dashes.
- Division by zero now raises a warning instead of an error.
- The || no longer concats strings but is Exclusive OR.
- For convenience, you may use dates like 2010-02-31.
- Replication - including bugs - is integrated.
- The parser now understands DESC and SHOW CREATE syntax.
- Porters did not finish the MySQL authentication system in the time allotted. DBAs will need to continue to use pg_hba.conf.
The following new features are activated by enabling mysql_compatible = on:
- The new black hole engine is an alias for /dev/null, and of course, is non-transactional.
- Table names depend on the underlaying filesystem: on Windows the table name is not case sentitive, on Unix "data", "Data" and "DATA" are different tables.
- Several release-critical bugs were introduced, to make debugging the application more fun.
- For convenience, PostgreSQL has provided a "root" user.
Some well-known PostgreSQL-features are incompatible with this new extension:
- ACID. Instead, MySQL ACID will be used. Committed data may or may not be written after a crash, depending on among other things, the current phase of the moon, the color of electrons and the weather conditions in Oz.
- Transactional DDL and DCL.
- User defined data types.
- For convenience, EXPLAIN output is printed on a single line.
- Schemas are no longer supported.
Certainly, support is available for all users of the new emulation layer. The license of this extension is pure BSD-style, allowing users to use and integrate "PostgreSQL with MySQL Emulation Layer" into your application. Dual-licensing is no longer source of trouble.