I am trying to concatenate a number of columns and skip the column if column is NULL OR empty in postgres. For example:
SELECT CONCAT(coalesce('a',''),
'|',coalesce('b',''),
'|',coalesce(NULL,''),
'|',coalesce('',''),
'|',coalesce('',''),
'|',coalesce('c','')) AS finalstring;
Output : a|b||||c
Expected output : a|b|c
Use concat_ws(), which ignores null values:
concat_ws('|', col1, col2, col3, ...)
If you want to ignore empty strings as well, then you can use nullif():
concat_ws('|', nullif(col1, ''), nullif(col2, ''), nullif(col3, ''), ...)