Drupal and PostgreSQL

Drupal module authors appear to be oblivious of PostgreSQL as they are using MySQL specific functions all over the place. Most of these problems can be solved by mimic MySQL functions by adding custom PostgreSQL functions. Drupal itself supplies a bunch of routines through the database installation routine.

CREATE FUNCTION from_unixtime(integer) RETURNS timestamp AS '

SELECT

$1::abstime::timestamp without time zone AS result

' LANGUAGE 'SQL';

CREATE FUNCTION year(timestamp with time zone) RETURNS double precision AS '

SELECT date_part(''year'', $1) AS result;

' LANGUAGE 'SQL';

CREATE FUNCTION rand() RETURNS double precision AS '

SELECT random();

' LANGUAGE 'SQL';

Module writers can alleviate this problem by using the function date_sql provided by the Date API

module found in the date package.

Another problem is the usage of mixing DISTINCT with ORDER BY rand(). PostgreSQL barfs on

that:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Example for such a query (from Acidfree):

SELECT DISTINCT(n.nid), n.title, n.created FROM node n INNER JOIN term_node tn ON n.nid = tn.nid WHERE tn.tid = 2 AND n.status = 1 ORDER BY RAND() LIMIT 1 OFFSET 0

This issue has been reported along with a patch as Error in SELECT statement under Postgresql.

Another problem with Acidfree is calling concat() with a single argument. The concat() Postgresql function defined by Drupal core accepts only two arguments.

This issue has been reported along with a patch as PostgreSQL error with concat while viewing images.