Thanks to you and Richard for pointing me in the right direction (I had the wrong syntax on the coalesce function) ...
toolate at night here (too early in the morning?) and I much appreciate the help.
The mysteries of NULL ...
Greg W.
-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: Fri 11/5/2004 2:15 AM
To: Gregory S. Williamson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Conactenating text with null values
On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:
> 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 together as 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 am missing
> (and I've been back and forth through the manual) would be most
> welcome.
I presume the empty columns are NULL. Anything concatenated with NULL
produces NULL. You should probably define columns that can be blank as
NOT NULL DEFAULT ''
With your current data you should use:
SELECT TRIM(TRIM(TRIM(COALESCE(s_house,'') || ' ' ||
COALESCE(s_post_dir,'')) || ' ' || COALESCE(s_street ,'')) || ' ' ||
COALESCE(s_suffix,'')) FROM parcels WHERE s_pin = '1201703303520';
The TRIMs are to remove surplus spaces from inside the result string.