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?

• Erwin Brandstetter: Related answer that may be of help: stackoverflow.com/q/12310986/939860. Note the last part about concat_ws() (and concat()) having function volatility STABLE (not IMMUTABLE), which prohibits their direct use in expression indexes.

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 ...

• ypercubeᵀᴹ: Note that the answer assumes that owner_addr1 is not null (the question seems to imply that, so it should be ok).
• Lennart - Slava Ukraini: Yes, I assumed that. Gets more complicated if that is not the case. CONCAT_WS looks like a neat function, reminds me of LISP:-)
• Jasen: the coalesce form can be used the three terms the same and then a substring chopping of the start