Does the || operator in Postgres result in a blank result if one of the columns is blank?

Question

I am running a query in Postgres using the || operator to concatenate some columns and it seems where one of the columns is blank the whole result is blank. Is that normal || behaviour?

With "blank" I mean NULL.

Answer 1

Depends what "blank" means, NULL or the empty string.

• If it means NULL, then yes, that's normal. SELECT 'ab' || NULL ; will return null.

• If it means an empty string, then no. SELECT 'ab' || '' ; will return 'ab'.

To avoid the issue, you can convert the nulls to empty string with COALESCE():

SELECT 
    COALESCE(str1, '') || COALESCE(str2, '') || ... || COALESCE(strN, '')

or use CONCAT() or CONCAT_WS() functions - which ignore nulls:

SELECT 
    CONCAT(str1, str2, ..., strN)

SELECT 
    CONCAT_WS('', str1, str2, ..., strN)