On this page
PostgreSQLism: CONCAT() can only take two arguments (D6 only, fixed in D7)
Drupal 7 will no longer be supported after January 5, 2025. Learn more and find resources for Drupal 7 sites
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.
Help improve this page
You can:
- Log in, click Edit, and edit this page
- Log in, click Discuss, update the Page status value, and suggest an improvement
- Log in and create a Documentation issue with your suggestion