PostgreSQL concat operator doesn’t like NULLs

I just found out that the || concatenation operator returns NULL if one of the inputs is NULL.

select '1' || '2'
==> null

Fortunately, Berend Tober added a user comment to the PostgreSQL documentation with a solution… rewrite the || operator!

CREATE OR REPLACE FUNCTION public.textcat_null(text, text)
  RETURNS text AS
$$$
SELECT textcat(COALESCE($1, ''), COALESCE($2, ''));
$$$
  LANGUAGE 'sql' VOLATILE;

CREATE OPERATOR ||(
  PROCEDURE = public.textcat_null,
  LEFTARG = text,
  RIGHTARG = text);

select '1' || '2'
==> 12

Works like a charm, thanks Berend!

Advertisement