Stefan Hornburg (Racke)
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
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.