Select Multiple columns into one field breaks if one or more columns are empty

Running PostgreSQL 7.4 (Yeah we are upgrading...)

Query:

SELECT (
     "Address" ||' '|| 
     "Address 2" ||' '|| 
     "City" ||' '|| 
     "State" ||' '|| 
     "Zip"
) AS full_address
FROM database
WHERE condition

All works as expected unless one or more of the fields are null/blank.

So if Address 2 is null/blank nothing is returned.

Question:

How can I use a IF (or CASE) condition to check if Address 2 has a value, if yes add it, el开发者_JAVA百科se skip

---

use coalesce:

select coalesce (someColumn, 'default-if-column-blank')

---

SELECT (
     "Address" ||' '|| 
     COALESCE("Address 2" ||' ', '') ||
     "City" ||' '|| 
     "State" ||' '|| 
     "Zip"
) AS full_address
FROM database
WHERE condition

http://www.postgresql.org/docs/7.4/static/functions-conditional.html#AEN11443