PostgreSQLism: CONCAT() can only take two arguments (D6 only, fixed in D7)

The CONCAT() is supported on both MySQL and Postgresql, but only with two parameters. Where you could write (in MySQL):

SELECT CONCAT('My', 'S', 'QL');

You have to write for Drupal:

SELECT CONCAT('Postgre', CONCAT('S', 'QL'))

Possible alternative

In PostgreSQL 8.4 and later, it's possible to create a CONCAT function which behaves the same as MySQL's.

CREATE FUNCTION CONCAT( VARIADIC ANYARRAY )
RETURNS TEXT
 LANGUAGE SQL
 IMMUTABLE
AS $function$
SELECT array_to_string($1,'');
$function$;

Note that the above may sometimes become confused about data types:

ERROR: could not determine polymorphic type because input has type "unknown"

The alternative is creating a CONCAT function for each reasonable data type (TEXT, VARCHAR, INTEGER).

This will be added to the mysqlcompat PostgreSQL project soon, which has many other functions which make supporting PostgreSQL easier for Drupal developers.

Standard concatenation operator

The SQL Standard has an operator for concatenation, which is easy to understand and quick to type. You just use the || operator, just like you'd use a + for integers:

SELECT 'Postgres' || 'Q' || 'L'

Drupal 7 runs MySQL in "strict mode", and supports the standard concatenation operator.