3

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.

6

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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