Warning Drupal now has a database abstraction layer. Please refer to the database API for more information on how to build modules for your favourite database.

PostgreSQL for modules

Hello everyone, I am writing this to explain to others how I got Drupal and most of the current modules up and running in a exclusively PostgreSQL environment.

First off you have to ask yourself is it really worth it? In most cases no absolutely not, MySQL is just fine for sites that are small and want to remain that way, this includes personal sites and blogs etc.

PostgreSQL's real strength at least in my opinion comes from its ability to seamlessly cluster, thereby distributing the load among many DB servers rather than bogging down a single server. If you have a big website with lots of traffic and have the resources for multiple servers, then PostgreSQL is definitely the way to go.

The first thing you want to do after deciding you want a particular module is to look at the SQL dump file and see if the table create statements are going to need to be converted from MySQL. In many cases the author is kind enough to include a proper .pgsql file, however there are many others who have not gone this little extra distance, and for this we need a proper conversion tool.

The tool that comes with pgsql for this is pretty weak, so I don't bother to use it. Also you need command line access which may not be something that is convenient at the time.

I use this tool, EasyMOD :: SQL Parser.
It does an excellent job of converting to/from MySQL and PostgreSQL.

It's far from perfect, and of course the MySQL dump files are not proper SQL, so you will have to work on it.
2 things that will be a constant annoyance. Table=MyISAM or Table=InnoDB those 2 types of statements are unknown and irrelevant to the rest of the SQL world, and should be removed prior to entering the SQL dump file into the conversion tool.

Another thing I've found that the tool can't understand is multibound keys, the statement looks like this
KEY nid(nid,mnid)
Should be changed to read
KEY (nid)

The ENUM datatype used by some of the dumps should be changed to
char(1)

And anything that has AUTO_INCREMENT should have AUTO_INCREMENT removed and have its type changed to SERIAL or BIGSERIAL.

Other than that, the converter works fantastic, just run your code through the converter, take the resulting SQL and paste it into whatever tool you use to query your DB.

After all that has been done, just test your modules, and see if they throw any errors, also check the DB and make sure no errant code is messing with the tables.

In 7/10 cases no patching is required, there are only 3 modules I can think of at present that need any patching at all. One was in ecommerce, the other was in User Points, the final one was in Terms & Conditions.

The most common thing to have to fix in modules is a non-sql compliant query.
In the only cases I have seen, the problem stems from an improper ORDER BY.

In MySQL "SELECT field1,field2,field3 FROM tablename WHERE condtion, ORDER BY field1" is perfectly valid syntax.
PostgreSQL will throw an error that looks something like
Query ERROR: Statement ORDER BY requires field2.

In this case, you will want to add field2 and field3 to the ORDER BY statement.
The resultant SQL should look something like this.
"SELECT field1,field2,field3 FROM tablename WHERE condition ORDER BY field1,field2,field3"

One other thing I have noticed is a NOT NULL violation, the only place I have seen this occur is in the terms and conditions module, after attempting to post your T&C,

This is caused by the module not placing a value in the T&C index field, and yet having the T&C index field set to not allow a NULL value. The best fix I have found to this is to ALTER the table at the tc_id column and change it to SERIAL. (If you get a "SERIAL is not a valid datatype" error or something like that the easiest solution then is to drop the entire table and remake with tc_id as SERIAL and conditions as TEXT).

Well those are all the tips I have at the moment, I hope you find this information useful.