Is it possible to write a SQL script for both MySQL and PostgreSQL?

Question

I'd like to write a single SQL script that will run on a default installation of either MySQL or PostgreSQL (versions 5.5 and 9.0, respectively). Is this possible?
I can almost do it by adding SET SESSION sql_mode='ANSI'; to the start of the script and using standard ANSI queries, but that line isn't valid for PostgreSQL. I could tell PostgreSQL to continue on errors, but It'd be nice to have a script that runs without error.

Answer 1

Try using conditional comments:

/*! SET SESSION sql_mode='ANSI'; */

PostgreSQL will ignore it, MySQL will run it. For more information see the docs.

Update: If you want to include commands that are run only on PostgreSQL but not on MySQL, you can exploit the fact that PostgreSQL supports nested comments, and MySQL doesn't. The following example shows how this could be used:

/*! SELECT 'MySQL' rdbms_type; */
/*/**/-- */ SELECT 'postgres' AS rdbms_type;

But this would probably make the file very difficult to read.

Answer 2

Is it possible to write a SQL script for both MySQL and PostgreSQL?

Yes.

Next question, please!

...

Okay, in all seriousness, it's totally doable, but you have to be aware of the things that each does differently. For example, if you need to use a bytea in PG, but a BLOB in MySQL, you're going to have a really fun time getting the encoding/escaping correct. Then there's things like fulltext searching. PG has it built in, MySQL has it built in to only one table type (MyISAM, the sucky one), and the syntax is totally different. And this doesn't even touch character sets and collations.

If you limit yourself to simple CRUD operations, you're probably good to go. Heck, if you've done your job right, you can also probably use the same exact code to talk to SQLite and MSSQL (when switched to ANSI mode).

Once you even get moderately complex, your code is going to need to at least be aware of the underlying database to work around the small behavior and syntax differences. The important part is that the majority of your queries can be shared between underlying databases without any modification whatsoever if you construct them properly.