How to write PostgreSQL-friendly SQL

With the release of Drupal 6 support for PostgreSQL has come a long way in that core and contrib modules no longer have to know PostgreSQL syntax for CREATE TABLE and other table/index creation or alteration actions.

However when it comes to writing actual SQL in core and contrib modules there will be many situations where SQL that works fine in MySQL will not work in PostgreSQL. Most of the time this is due to the use of MySQL-specific SQL functions or to PostgreSQL's stricter adherence to SQL 92 or SQL 99 standards.

Now that D6 is out it's likely that many module authors will begin to port their modules to D6. With the schema API addition to D6, most authors will assume that PostgreSQL support is included without thinking about the SQL they write in their module functions. For those of us that are PostgreSQL supporters and have the knowledge to help module authors with their SQL this is a great time to make a push for more solid support of PostgreSQL in Drupal.

This Wiki can help to identify different types of SQL that will have problems in PostgreSQL and include solutions if any to these problems. Please add or update anything you think is helpful.

Multiple value INSERTs

• In MySQL (and in PostgreSQL 8.2+) it's possible to insert multiple rows in a single INSERT statment. This is not available in PostgreSQL < 8.2

INSERT IGNORE

• MySQL allows you to use INSERT IGNORE to insert rows into a table and instead of causing an error when attempt to insert a row with a duplicate of a primary key or unique column the insert for that row is ignored.
  • http://dev.mysql.com/doc/refman/5.0/en/insert.html

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued

• PostgreSQL has no such functionality. The easiest workaround is to replace the INSERT IGNORE with a DELETE and INSERT
  
  DELETE FROM foo WHERE bar = 1 AND baz = 2
INSERT INTO foo (bar, baz) VALUES (1, 3)

REPLACE INTO

• MySQL allows you to use REPLACE [INTO] in place of an INSERT. http://dev.mysql.com/doc/refman/5.0/en/replace.html

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

• No such function exists for PostgreSQL. The likely workaround would be the same as INSERT IGNORE, to replace with a DELETE and INSERT or a SELECT to find if row exists and UPDATE or INSERT if row does not exist.

UNIX_TIMESTAMP

• MySQL has a date/time function UNIX_TIMESTAMP to return the current time in UNIX timestamp format. This is essentially the same as the PHP time() function.
• PostgreSQL has a method of extracting the UNIX timestamp but it's a bit more involved than the MySQL UNIX_TIMESTAMP
  • SELECT EXTRACT('epoch' FROM CURRENT_TIMESTAMP)
• Suggested workaround is to pass in the UNIX timestamp from PHP (which is the better practice anyway, it keeps you from running into time offset issues between your web server and your database server)
  
  $result = db_query('SELECT foo, bar FROM baz WHERE created > UNIX_TIMESTAMP - 86400 * 7');

replace with

$time = time();
$result = db_query('SELECT foo, bar FROM baz WHERE created > %d - 86400 * 7', $time);

Date math

• Both MySQL and PostgreSQL have a number of ways to do date arithmetic. Unfortunately they are different enough that whenever needed, it's best to try and accomplish any date math in PHP where possible.
  • http://www.postgresql.org/docs/8.1/static/functions-datetime.html
  • http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

GROUP BY

• MySQL allows you to SELECT columns that are then not explicitly stated in the GROUP BY clause. PostgreSQL does not
  • http://www.postgresql.org/docs/8.1/static/sql-select.html

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

• Workaround is to simply add the additional columns to the GROUP BY clause.

General query syntax

• MySQL optionally uses the backtick (`) to delimit table and field names. PostgreSQL does not.
• Workaround: omit backticks from your queries, and instead make sure that your fieldnames do not correspond to any reserved words: http://drupal.org/node/141051