Converting from MySQL to PostgreSQL.

I did it both ways and have converted other databases to MySQL.

In the early days of MySQL I converted a MySQL database to PostgreSQL so that I could use transactions but PostgreSQL was too hard to install on Windows workstations so I converted back when MySQL gained transactions. The conversion in both directions was difficult because MySQL worked with SQL 92 and PostgreSQL worked with SQL 99. If you used the full features of PostgreSQL then a conversion to MySQL was horrible. If you did not use the full features of PostgreSQL then there was no advantage in using PostgreSQL.

Today MySQL release 5 supports more of the SQL 99 features I like to use and PostgreSQL is easier to install on Windows which means you can consider PostgreSQL as a replacement for Microsoft Access style databases and can deploy private copies for all your developers. A conversion between MySQL 5 and PostgreSQL should be easier.

If you are using MySQL 4 then consider changing to MySQL 5, then converting your SQL to PostgreSQL compatible SQL and then changing to PostgreSQL at a later date. Using MySQL 5, you can stage the conversion over a longer time.

Many Drupal modules have both MySQL and PostgreSQL versions of SQL. You can look at them for examples of the difference. Replace "int(10) unsigned" with "integer" etc.

If you manage your MySQL with phpMyAdmin, http://www.phpmyadmin.net/, then install phpPgAdmin, http://phppgadmin.sourceforge.net/, so you can export from one and import into the other.

The following code shows the start of a simple function you could use to make SQL compatible if the structure of the SQL is the same. You can translate from MySQL to PostgreSQL because you are using the simpler SQL in MySQL. For example with MySQL 4 you are not using nested selects and in MySQL 5 you can use the same nested selects as in PostgreSQL. Automated translation from PostgreSQL to MySQL would be nearly impossible because the PostgreSQL SQL can contain features not in MySQL 4 or not yet fully implemented in MySQL 5.

/* Copyright PeterMoulding.com 2006
Free for use with Drupal (drupal.org).
*/
/**
 * Implementation of hook_install()
 */
function artist_install()
	{
	global $db_type;
	db_query(artist_install_sql("create table {artist}
		(
		vid <integer> not null,
		actor boolean not null default 0,
		musician boolean not null default 0,
		producer boolean not null default 0,
		singer boolean not null default 0,
		writer boolean not null default 0,
		web_site tinytext not null default '',
		primary key (vid));"), $db_type);
	drupal_set_message(t('Artist module created artist table.'));
	}
/* Copyright PeterMoulding.com 2005
Free for use with Drupal (drupal.org).
*/
function artist_install_sql($sql, $db_type = '')
	{
	$default = '';
	$dbs[$default] = array('<integer>' => 'int(10) unsigned'); // The default plus mysql and mysqli.
	$dbs['pgsql'] = array('<integer>' => 'integer');
	if(isset($dbs[$db_type]))
		{
		$db = $dbs[$db_type];
		}
	else
		{
		$db = $dbs[$default];
		}
	foreach($db as $replace => $with)
		{
		$sql = str_ireplace($replace, $with, $sql);
		}
	return $sql;
	}

petermoulding.com/web_architect