Archiv für April, 2009

A better CONCAT for PostgreSQL

Concatination 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:

  1. CREATE TABLE persons (
  2. id        SERIAL      PRIMARY KEY,
  3. firstname TEXT        NOT NULL,
  4. surname   TEXT        NOT NULL,
  5. title     TEXT
  6. );
  7.  
  8. INSERT INTO persons (firstname, surname, title)
  9. VALUES ('Edward', 'Hyde', NULL);
  10. INSERT INTO persons (firstname, surname, title)
  11. VALUES ('Henry', 'Jekyll', 'Dr.');
  12.  
  13. SELECT * FROM persons;
  14.  
  15. id | firstname | surname | title
  16. ----+-----------+---------+-------
  17. 1 | Edward    | Hyde    |
  18. 2 | Henry     | Jekyll  | Dr.
  19. (2 rows)
  20.  
  21. SELECT title || ' ' || firstname || ' ' || surname FROM persons;
  22.  
  23. ?COLUMN?
  24. ------------------
  25.  
  26. Dr. Henry Jekyll
  27. (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:

  1. CREATE OR REPLACE FUNCTION concat(text, text, text) RETURNS text AS $$
  2. SELECT $1 || ' ' || $2 || ' ' || $3;
  3. $$ LANGUAGE 'sql';
  4.  
  5. SELECT CONCAT(title, firstname, surname) FROM persons;
  6. concat
  7. ----------------
  8.  
  9. Dr. Henry Jekyll
  10. (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:

  1. SELECT ARRAY_TO_STRING(ARRAY[title, firstname, surname], ' ') FROM persons;
  2.  
  3. array_to_string
  4. ------------------
  5. Edward Hyde
  6. Dr. Henry Jekyll
  7. (2 rows)

Problem solved!