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?

Related answer that may be of help: https://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. — Erwin Brandstetter, May 14 '18 at 01:28

ypercubeᵀᴹ · Accepted Answer · 2018-05-03T08:39:35.847

16

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)

edited May 03 '18 at 08:39

answered May 03 '18 at 08:31

ypercubeᵀᴹ

92,106
13
189
284

Lennart · Answer 2 · 2018-05-03T08:31:22.913

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

Note that the answer assumes that `owner_addr1` is not null (the question seems to imply that, so it should be ok). — ypercubeᵀᴹ, May 03 '18 at 08:36
Yes, I assumed that. Gets more complicated if that is not the case. CONCAT_WS looks like a neat function, reminds me of LISP:-) — Lennart, May 03 '18 at 08:42
the coalesce form can be used the three terms the same and then a substring chopping of the start — Jasen, May 03 '18 at 09:48

Conditional string concatenation in PostgreSQL

2 Answers2