SQL compatibility reminder: MySQL vs PostgreSQL - Mailing list pgsql-hackers

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am not
looking for a flame war.

I would like to point out Drupal community efforts (including myself) to
write down the most frequent problems when porting MySQL from/to
PostgreSQL:

The main MySQL/PostgreSQL issues can be found here:
http://drupal.org/node/555514

An important pending issue, which goes on and on for years:

=> All non-aggregate fields must be present in the GROUP BY clause
http://drupal.org/node/555530

These ones are less urgent, but still needed to ease migration:

=> Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM
(SELECT DISTINCT ... ) ... for multiple
http://drupal.org/node/706264

=> DELETE SYNTAX on several tables requires the USING syntax:
http://drupal.org/node/555562

IMHO, it is no use working on complex issues like replication if the SQL
code of major softwares cannot run on PostgreSQL. 

IMHO, 99% Drupal developers do not have a deep knowledge in SQL:

* For example, part of Drupal developers are trying to find an automated
way to insert DISTINCT on queries automatically using PHP preg. Of
course, this creates bugs, which go on and on for years. The attempt can
be seen here: http://drupal.org/node/284392 (>400 replies). It could
well be 10 years more bugs in this thread.

* Another very funny thing from Drupal community is that MySQL trims
data without complaining, which is not the case for PostgreSQL:
http://drupal.org/node/279219

But there is no way to change people. It looks like PostgreSQL SQL
syntax and parser should evolve to become more tolerant.

If PostgreSQL syntax was more tolerant, Drupal developers would be
interested in leaving MySQL for PostgreSQL. SO PLEASE take a deep look
at my request.

So what are your plans for PostgreSQL 9? Do you finally plan to beat
MySQL?

Kind regards,
Jean-Michel Pouré




pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Explicit psqlrc
Next
From: "Greg Sabino Mullane"
Date:
Subject: Re: machine-readable pg_controldata?