8/30/2023 0 Comments Postgresql string concatenationExtending 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |