Conactenating text with null values - Mailing list pgsql-general

This is postgres 7.4 on a linux box ...

I have driven myself to distraction trying to what ought to be easy.

I have a table with house number, street direction, street name and street suffix as 4 columns. I want to paste them
togetheras one text string for use by another application. 

SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin = '1201703303520';
 s_house | s_post_dir |    s_street    | s_suffix
---------+------------+----------------+----------
 34643   |            | FIG TREE WOODS |

So to get "34643 FIG TREE WOODS" what do I do ?

SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' || s_suffix FROM parcels WHERE s_pin = '1201703303520';
 ?column?
----------

(1 row)

I have tried all manner of COALESCE and various trickeries. Nothing works. In Informix this works exactly as I think it
should.Is Informix totally whack, or what ?  

I guess I will have to code a perl script to do this seemingly straightforward operation. Any suggestions as to what i
ammissing (and I've been back and forth through the manual) would be most welcome. 

Thanks,

Greg Williamson
DBA (hah!)
GlobeXplorer LLC