Conditional string concatenation in PostgreSQL

Question

I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new field, owner_addr where each of the above fields is concatenated with // between each of them.

But if one or more of the original columns is NULL, I don't want to concatenate // to the result column. So for instance, if owner_addr1 is 123 4th Avenue SE and owner_addr2 and owner_addr3 are NULL, then I want the result column to just be 123 4th Avenue SE, not 123 4th Avenue SE // // (which would happen if I just did CONCAT() with // between the NULL strings ... I only want to add // between non-NULL columns, or leave it out altogether if there is only one non-NULL column.

Is there an easy way to do this kind of conditional concatenation in Postgresql, where it leaves out empty lines? Or should I write a python script to do this?

Answer 1

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored:

select concat_ws('//', owner_addr1, owner_addr2, owner_addr3)

Test:

red=# select concat_ws('//', 'abc', null, null, 'xx', null, 'xyz', null) 
          as address;
   address    
--------------
 abc//xx//xyz
(1 row)

Answer 2

Probably most obvious to use a CASE statement. There are 4 cases:

owner_addr2 IS NULL AND owner_addr3 IS NULL => ''       
owner_addr2 IS NULL AND owner_addr3 IS NOT NULL => '//' || owner_addr3
owner_addr2 IS NOT NULL AND owner_addr3 IS NULL => '//' || owner_addr2
owner_addr2 IS NOT NULL AND owner_addr3 IS NOT NULL '//' || owner_addr2 || '//' owner_addr3

SELECT owner_addr1 
    || CASE WHEN owner_addr2 IS NULL AND owner_addr3 IS NULL
            THEN ''
            WHEN owner_addr2 IS NULL AND owner_addr3 IS NOT NULL
            THEN '//' || owner_addr3
            WHEN owner_addr2 IS NOT NULL AND owner_addr3 IS NULL 
            THEN '//' || owner_addr2   
            WHEN owner_addr2 IS NOT NULL AND owner_addr3 IS NOT NULL 
            THEN '//' || owner_addr2 || '//' || owner_addr3
       END AS owner_addr
FROM ...

An alternative is to use 2 CASE statements:

SELECT owner_addr1 
    || CASE WHEN owner_addr2 IS NULL 
            THEN '' 
            ELSE '//' || owner_addr2
       END
    || CASE WHEN owner_addr3 IS NULL 
            THEN '' 
            ELSE '//' || owner_addr3
       END as owner_addr
FROM ...

COALESCE can be used instead of CASE:

SELECT owner_addr1 
    || COALESCE('//' || owner_addr2, '')
    || COALESCE('//' || owner_addr3, '') as owner_addr
FROM ...