String concatenation using pipe “||” operator in PostgreSQL

PostgreSQL supports pipe “||” operator which is also called as string concatenation operator. PostgreSQL database also has CONCAT function to perform string concatenation.

postgres=# SELECT 'This'||' is string'||' concat'||' example' result;
            result             
-------------------------------
 This is string concat example
(1 row)
postgres=# SELECT concat(concat(concat('This', ' is string'), ' concat'), ' example') result;
            result             
-------------------------------
 This is string concat example
(1 row)

Would’t be using CONCAT function to combine hundreds of strings be complicated. This is the reason why Pipe "||" operator is commonly used in database string concatenation. But does the behavior of CONCAT function and Pipe operator same in PostgreSQL?

postgres=# SELECT concat(concat(concat('This', ' is string'), null), ' example') result;
         result         
------------------------
 This is string example
(1 row)
postgres=# SELECT 'This'||' is string'||NULL||' example' result;
 result 
--------
 
(1 row)

Isn’t the output strange? CONCAT function ignores NULLs, but Pipe operator in PostgreSQL can not handle NULL. You need to handle explicitly when using Pipe operator.

postgres=# SELECT 'This'||' is string'||coalesce(NULL, '')||' example' result;
         result         
------------------------
 This is string example
(1 row)

Literally using coalesce during string concatenation may make your code look nasty. This is why PostgreSQL provides concat_ws function to deal with such situation.

postgres=# SELECT 'This'||' is string'||coalesce(NULL, '')||' using'||coalesce(NULL, '')||' pipe operator'||coalesce(NULL, '')||' example' result;
                   result                   
--------------------------------------------
 This is string using pipe operator example
(1 row)

Below code uses CONCAT_WS function.

postgres=# SELECT concat_ws('', 'This', ' is string', NULL, ' using', NULL, ' pipe operator', NULL, ' example') result;
                   result                   
--------------------------------------------
 This is string using pipe operator example
(1 row)

Still not happy. Looking for other easy and efficient approaches towards string concatenation, then continue reading below.

Other ways to deal with string concatenation ignoring NULLs

PostgreSQL database provides the option of extending the set of functions and operators available. Creating custom functions or operators in PostgreSQL is not difficult at all.

Extending Pipe operator and building custom function

Extending Pipe operator simply means, extending its functionality beyond its current usage. In order to do that, create custom operators, custom functions to make more efficient and legible SQL statements in an effective way.

Below is an example of a user-defined function and custom Pipe operator.

CREATE SCHEMA pgext;

CREATE OR REPLACE FUNCTION pgext.concatnull(text, text)
RETURNS text LANGUAGE sql immutable as $$
  select COALESCE ($1 , '') operator(pg_catalog.||) COALESCE ($2 , '')
$$;

GRANT EXECUTE ON FUNCTION pgext.concatnull(text, text) TO PUBLIC;

CREATE OPERATOR pgext.|| (
LEFTARG=TEXT,
RIGHTARG=TEXT,
PROCEDURE = pgext.concatnull);

SELECT set_config('search_path', 'pgext, pg_catalog,'||current_setting('search_path'), false);

postgres=# SELECT 'abc' || NULL || 'def' AS res;
  res   
--------
 abcdef
(1 row)

Building another string concat operator in PostgreSQL

Another interesting and elegant approach is to create an alternate version of the Pipe operator.

CREATE OR REPLACE FUNCTION pipe_ext ( text, text )
RETURNS text AS
'SELECT
CASE WHEN $1 IS NULL THEN $2
WHEN $2 IS NULL THEN $1
ELSE $1 || '''' || $2 END;'
LANGUAGE sql IMMUTABLE;

Then create a custom operator "||+".

CREATE OPERATOR ||+ (LEFTARG = TEXT, RIGHTARG = TEXT, PROCEDURE = pipe_ext);

Use this new operator in a query is very easy and will produce a more natural result.

postgres=# SELECT 'abc' || NULL || 'def' AS res;
 res 
-----
 
(1 row)
postgres=# SELECT 'abc' ||+ NULL ||+ 'def' AS res;                                                                           
  res   
--------
 abcdef
(1 row)

Conclusion

If you are familiar with Oracle database then you may find this article bit confusing. Read NULL in Oracle vs. NULL in PostgreSQL article to understand how NULL in PostgreSQL behaves compared to Oracle database.

If you are working on Oracle to PostgreSQL database migration requirements and confused with NULL handling behavior, you can read our NULL Handling chapter to get more insight into NULLs and their semantically and behaviourally difference.

The purpose of this post is to make you understand about the fact about NULL in PostgreSQL and handle NULLs carefully when writing queries involving string concatenation.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments