6

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?

  • 1
    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. – Erwin Brandstetter May 14 '18 at 1:28
12

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)
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 ...
  • Note that the answer assumes that owner_addr1 is not null (the question seems to imply that, so it should be ok). – ypercubeᵀᴹ May 3 '18 at 8:36
  • 1
    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 3 '18 at 8:42
  • 1
    the coalesce form can be used the three terms the same and then a substring chopping of the start – Jasen May 3 '18 at 9:48

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.