Q:

How to include empty string in string_agg?

I've done a groklearning exercise up until the point of:

Make sure to list all properties, even if there is some unknown
  address information; replace any missing parts with an empty string

Could someone guide me on how to make this work?
SELECT D.council_name, string_agg( P.house_number || ' ' || P.street  || ' ' ||    L.locality,  ', ' ORDER BY S.contract_date) AS commercial_properties
FROM Sales S, Properties P, Districts D, Localities L
WHERE S.property_id = P.property_id
AND P.locality_id = L.locality_id
AND P.district_code = D.district_code
AND P.primary_purpose = 'COMMERCIAL'
AND S.contract_date BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY D.council_name;

But it prints:
+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY                                                                |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

Instead of:
+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY, MACWOOD RD SMITHS LAKE                                        |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

It doesn't print "MACWOOD RD SMITHS LAKE" as it is missing the house_number.  How can I add the missing parts to the results?

A:

You can use COALESCE to map null to an empty string for all attributes that may contain null:
string_agg( COALESCE(P.house_number, '') 
         || ' ' 
         || COALESCE(P.street, '')  
         ...
) AS commercial_properties

