PostgreSQL 中的條件字元串連接

我有一個表parcels，其中目前包含列owner_addr1, owner_addr2, owner_addr3。有時，後兩個欄位中的一個或兩個為空。我想將它們組合成一個新欄位，owner_addr其中上述每個欄位//都在它們之間連接。

但是如果一個或多個原始列是 NULL，我不想連接//到結果列。因此，例如，如果owner_addr1is123 4th Avenue SE和are owner_addr2，那麼我希望結果列只是 be ，而不是（如果我只是在 NULL 字元串之間進行操作，就會發生這種情況……我只想在非列之間添加，或者保留它如果只有一個非列，則完全排除。owner_addr3``NULL``123 4th Avenue SE``123 4th Avenue SE // //``CONCAT()``//``//``NULL``NULL

有沒有一種簡單的方法可以在 Postgresql 中進行這種條件連接，它會省略空行？或者我應該寫一個python腳本來做到這一點？

該功能concat_ws()完全符合您的要求。第一個參數用作其餘參數之間的粘合劑。空值被忽略：

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

測試：

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

使用 CASE 語句可能是最明顯的。有4種情況：

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

另一種方法是使用 2 CASE 語句：

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 代替 CASE：

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

引用自：https://dba.stackexchange.com/questions/205670