Skip to content

PostgreSQL 9.0: Includes the new MySQL Emulation Layer

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
unit            |
category     | Version AND Platform Compatibility / Other Platforms AND Clients
short_desc | Enable MySQL Emulation Layer
extra_desc |
context       | backend
vartype      | bool
min_val      |
max_val     |

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.


MadBlog on : PostgreSQL projesinde gelişmeler

Show preview
PostgreSQL 9.1 sürümü ile birlikte NoSQL'e geçiyor: 9.0 sürümünde MySQL Emulation Layer geliyor: Comments ()

ads' corner on : About this new MySQL Emulation Layer in PostgreSQL 9.0

Show preview
This blog post of mine about the new MySQL Emulation Layer in PostgreSQL 9.0 was (more of less obvious) an april joke. Most readers have guessed right. On the other hand the idea, that this joke is based, is very real: during the last conferences and exhi Comments () on : PingBack

Show preview
Comments ()


Display comments as Linear | Threaded

Thom Brown on :

Andreas, you're an evil genius! :)
Comments ()

Gerald Butler on :

Awesome! Thank Stallman! Now my applications can work properly.
Comments ()

Theory on :

Sign me up! —Theory
Comments ()

digicon on :

Comments ()

PerlBoy on :

And will be disabled next year at APRIL 1 again? :P
Comments ()

Larry Ellison on :

I demand you remove this. Those behaviours are our sole property and we do not authorise their use. Cease and desist.
Comments ()

Devrim GÜNDÜZ on :

Thanks for making my day better :)
Comments ()

gray_hemp on :

Oh! I was close to believe last night :) Translated it to Russian in my blog
Comments ()

Stephan Jaensch on :

There are so many legitimate concerns about MySQL, why include phony ones as well? "Non-aggregate columns in the SELECT list need no longer appear in the GROUP BY clause." That is a great MySQL feature (if you know what you're doing). That blog post you linked it kind of disingenious - of course MySQL has to "guess", but only if you don't provide ordering. PostgreSQL has DISTINCT ON, which works similarly, but doesn't solve all use cases as well as MySQL - and does the exact same thing as MySQL if you don't provide explicit ordering, namely return a largely arbitrary result! Pot, meet kettle. In fact, PostgreSQL DISTINCT ON is at its core IDENTICAL with MySQLs GROUP BY syntax extension.
Comments ()

Andreas 'ads' Scherbaum on :

Stephan, just check the date ... And to pick up your point: of course, if would be a great feature, if it would allow predictable results. It does not.
Comments ()

AndersH on :

When I first read the item, I thought it did something like implicitly adding the columns to the group by, so "select a, b, sum(c) from foo" would be interpreted as "select a, b, sum(c) from foo group by a, b". This is something i often have wished (but I aren't sure it wouldn't have unintentioned side effects). But reading the link, it was of course clear that what they do (i.e. treating it as "select first(a), first(b), sum(c) from foo") is not the right way.
Comments ()

Stephen on :

> That is a great MySQL feature No, it's not. It's a sign that either your design or your thought process is severely broken. Just yesterday I had to clean up the mess from a program bug where another developer decided this sort of query (using a MySQL database) was a good idea. Of course, MySQL has been inbreeding this sort of broken thinking since its inception.
Comments ()

greg on :

When will it be possible to have : CTRL + C leave the current query and also leave the command line client (in fact, I think we can even go further and reboot the computer, that would be more fun) ? Have the CLI to remember the \e but not the queries you edited ? Have only one error message for all foreign key types of errors ? support different encodings between tables so we cannot foreign key with varchars ? Do not allow multiple autoincrement values on one table ? Forget about inheritance ? Forget about generate_series ? I am sure without all the unecessary features, my computer will , maybe not, be faster. Thank in advance ! Oh ... I forgot ... the support of «FLUSH PRIVILEGES» would definitely make Pg to look like a genuine MySQL database.
Comments ()

Frank on :

Events and stored procedures will not be backuped by default anymore? Nobody needs that stuff anyway, way too difficult. ;)
Comments ()

dnm on :

Comments ()

dude on :

Is there an option for transparently truncating data that doesn't fit into a field? I hate all those lame errors I keep getting in Postgres.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options