Concatenation is one thing you need quite often when working with databases and especially when you have to generate some reports. And even as it seems to be a simple problem looking for an easy solution it’s a pithole. The natural way would be to do something like this:
CREATE TABLE persons ( id SERIAL PRIMARY KEY, firstname TEXT NOT NULL, surname TEXT NOT NULL, title TEXT ); INSERT INTO persons (firstname, surname, title) VALUES ('Edward', 'Hyde', NULL), ('Henry', 'Jekyll', 'Dr.'); SELECT * FROM persons; id | firstname | surname | title ----+-----------+---------+------- 1 | Edward | Hyde | 2 | Henry | Jekyll | Dr. (2 rows) \pset null <NULL> SELECT title || ' ' || firstname || ' ' || surname AS fullname FROM persons; fullname ------------------ <NULL> Dr. Henry Jekyll (2 rows)
As you see, the row containing the NULL value is omitted. So how to fix that? Of course we might do something like using COALESCE to return at least an empty string instead of a NULL value, but this way we’d get a whitespace we might not want (on the web not that bad because normaly the browser renders multiple whitespace as one).
So, if you research this issue a bit via google you’ll find many solutions. From PL/pgSQL CONCAT functions with might handle two, three or four arguments (CONCAT2, CONCAT3, you get the picture) to solutions using custom aggregate functions. But most of them don’t care about NULL values and will break.
Let’s try this with a simple CONCAT function:
CREATE OR REPLACE FUNCTION concat(text, text, text) RETURNS text AS $$ SELECT $1 || ' ' || $2 || ' ' || $3; $$ LANGUAGE 'sql'; SELECT CONCAT(title, firstname, surname) AS fullname FROM persons; fullname ------------------ <NULL> Dr. Henry Jekyll (2 rows)
Still, the same problem with the row containing the NULL value.
While looking into this, I ran over the buildin function ARRAY_TO_STRING which leads the path to a very straight forward solution:
SELECT ARRAY_TO_STRING(ARRAY[title, firstname, surname], ' ') AS fullname FROM persons; fullname ------------------ Edward Hyde Dr. Henry Jekyll (2 rows)
This post has been adopted and updated from my personal blog where it was posted on April 1st, 2009.