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.


1 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():

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

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.