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

Set the mode in the server not in the script:

You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option, or by using sql-mode="modes" in my.cnf (Unix operating systems) or my.ini (Windows).

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

Obviously in the script your going to have use the lowest common dominator of features that both accept. MySQL is also known to have the most standard compliant parser but that does not mean it actually mean it will do anything (Good way to get Gotcha'd since the scripts will both work but behaviour may be totally different). Postgresql is not 'ansi' compliant either. It may come the closest but it has plenty of things unique to itself. Does not seem like an ideal way.

ORM's work hard at doing this same kind of thing - taking the pain out of it.

Answer 3

I think you are in for a one-by-one feature comparison. I didn't read the whole link but I think it might be usefull in your quest.

http://troels.arvin.dk/db/rdbms/

Answer 4

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.

Answer 5

Adding

SET SESSION sql_mode='ANSI';

Without the conditions (?) solves the ulogin php library install problem. Also helpful to add use database; if you already created it on the command line.