CONCAT_WS for PostgreSQL

PostgreSQL do not have function like CONCAT_WS of MySQL, but writing the query as given below we can do much more...


SELECT ARRAY_TO_STRING(ARRAY[initial, firstname, lastname] , ' ') AS user
FROM users

Same as CONCAT_WS function, the above query will ignore fields which are having NULL values, and return the concatenation of remaining fields. But if your fields contains blank value, modify the above query as given below, to get the proper result

SELECT ARRAY_TO_STRING(ARRAY[
CASE title WHEN '' THEN NULL ELSE title END,
CASE firstname WHEN '' THEN NULL ELSE firstname END,
CASE lastname WHEN '' THEN NULL ELSE lastname END], ' ') AS user
FROM users