CREATE OR REPLACE FUNCTION pipe_ext ( text, text ) Postgres=# SELECT 'abc' || NULL || 'def' AS res īuilding another string concat operator in PostgreSQLĪnother interesting and elegant approach is to create an alternate version of the Pipe operator. SELECT set_config('search_path', 'pgext, pg_catalog,'||current_setting('search_path'), false) GRANT EXECUTE ON FUNCTION ncatnull(text, text) TO PUBLIC Select COALESCE ($1, '') operator(pg_catalog.||) COALESCE ($2, '') RETURNS text LANGUAGE sql immutable as $$ CREATE SCHEMA pgext ĬREATE OR REPLACE FUNCTION ncatnull(text, text) In order to do that, create custom operators, custom functions to make more efficient and legible SQL statements in an effective way.īelow is an example of a user-defined function and custom Pipe operator. Extending Pipe operator and building custom functionĮxtending Pipe operator simply means, extending its functionality beyond its current usage. Creating custom functions or operators in PostgreSQL is not difficult at all. PostgreSQL database provides the option of extending the set of functions and operators available. Other ways to deal with string concatenation ignoring NULLs Looking for other easy and efficient approaches towards string concatenation, then continue reading below. postgres=# SELECT concat_ws('', 'This', ' is string', NULL, ' using', NULL, ' pipe operator', NULL, ' example') result This is string using pipe operator exampleīelow code uses CONCAT_WS function. postgres=# SELECT 'This'||' is string'||coalesce(NULL, '')||' using'||coalesce(NULL, '')||' pipe operator'||coalesce(NULL, '')||' example' result This is why PostgreSQL provides concat_ws function to deal with such situation. Literally using coalesce during string concatenation may make your code look nasty. postgres=# SELECT 'This'||' is string'||coalesce(NULL, '')||' example' result You need to handle explicitly when using Pipe operator.

Isn’t the output strange? CONCAT function ignores NULLs, but Pipe operator in PostgreSQL can not handle NULL. (1 row) postgres=# SELECT 'This'||' is string'||NULL||' example' result But does the behavior of CONCAT function and Pipe operator same in PostgreSQL? postgres=# SELECT concat(concat(concat('This', ' is string'), null), ' example') result This is the reason why Pipe '||' operator is commonly used in database string concatenation. Would’t be using CONCAT function to combine hundreds of strings be complicated. (1 row) postgres=# SELECT concat(concat(concat('This', ' is string'), ' concat'), ' example') result postgres=# SELECT 'This'||' is string'||' concat'||' example' result PostgreSQL database also has CONCAT function to perform string concatenation. PostgreSQL supports pipe “||” operator which is also called as string concatenation operator.

Pgsql concat