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.

Thanks,

Greg Williamson
DBA (hah!)
GlobeXplorer LLC


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Gregory S. Williamson wrote:
If the blank fields are null then Informix is wrong. String concatenated
with null gives null.

SELECT coalesce(s_house,'') || ' ' || coalesce(s_post_dir,'') ...

To be honest, if the address fields are blank then they should be set to
the empty string. They're not "unknown" they're empty.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:
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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thanks to you and Richard for pointing me in the right direction (I had thewrong syntax on the coalesce function) ... too late 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 [mailtolly (AT) lfix (DOT) co.uk]
Sent: Fri 11/5/2004 2:15 AM
To: Gregory S. Williamson
Cc: pgsql-general (AT) postgresql (DOT) org
Subject: Re: [GENERAL] Conactenating text with null values
On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:
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.






---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, Nov 05, 2004 at 01:25:07AM -0800, Gregory S. Williamson wrote:

This query almost works:

SELECT COALESCE(s_house, '') || ' ' ||
COALESCE(s_post_dir, '') || ' ' ||
COALESCE(s_street, '') || ' ' ||
COALESCE(s_suffix, '')
FROM parcels WHERE s_pin = '1201703303520';
?column?
------------------------
34643 FIG TREE WOODS

However, the result has excess spaces where the NULL fields are.
You could use functions like REPLACE(), LTRIM(), and RTRIM() to
get rid of extra spaces, but it might be easier write a function
to build the address string from only the non-NULL components:

SELECT buildaddr(s_house, s_post_dir, s_street, s_suffix)
FROM parcels WHERE s_pin = '1201703303520';
buildaddr
----------------------
34643 FIG TREE WOODS

Here's a PL/Perl implementation of buildaddr():

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
return join(" ", grep {defined} @_);
' LANGUAGE plperl;

Here's a PL/pgSQL implementation; maybe somebody can improve on it:

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
DECLARE
addr TEXT[] := ''{}'';
BEGIN
IF $1 IS NOT NULL THEN
addr := array_append(addr, $1);
END IF;

IF $2 IS NOT NULL THEN
addr := array_append(addr, $2);
END IF;

IF $3 IS NOT NULL THEN
addr := array_append(addr, $3);
END IF;

IF $4 IS NOT NULL THEN
addr := array_append(addr, $4);
END IF;

RETURN array_to_string(addr, '' '');
END;
' LANGUAGE plpgsql;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

SELECT COALESCE(s_house,'') || COALESCE(s_post_dir,'') || COALESCE(s_street,'') || COALESCE(s_suffix,'') FROM parcels WHERE s_pin = '1201703303520';

Gregory S. Williamson schrieb:
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

[snip]

SELECT TRIM(
COALESCE(s_house || ' ','')

Looks a bit more understandable :-)

Cheers,
Csaba.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Fri, Nov 05, 2004 at 12:51:11PM +0100, Csaba Nagy wrote:
But it's still too cumbersome. How about creating a new operator? With
the example below the query would simply be

SELECT s_host ||~ s_post_dir ||~ s_street ||~ s_suffix
FROM parcels
WHERE s_pin = '1201703303520';

alvherre=# CREATE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql;
CREATE FUNCTION
alvherre=# SELECT text_concat_nulls_with_an_embedded_space('foo', NULL);
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space(NULL, 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space('bar', 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
bar foo
(1 fila)
alvherre=# CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR
alvherre=# SELECT 'hi' ||~ 'foo' ||~ null ||~ null ||~ 'bar' ||~ 'baz' ||~ null ||~ 'bye';
?column?
--------------------
hi foo bar baz bye
(1 fila)


--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The first of April is the day we remember what we are
the other 364 days of the year" (Mark Twain)


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Cool, this goes to my "util" mail folder :-)

[snip]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sweet. I learn something every day. thanks for ideas, one and all!
G
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre (AT) dcc (DOT) uchile.cl]
Sent: Fri 11/5/2004 8:49 AM
To: Csaba Nagy
Cc: olly (AT) lfix (DOT) co.uk; Gregory S. Williamson; Postgres general mailing list
Subject: Re: [GENERAL] Conactenating text with null values
On Fri, Nov 05, 2004 at 12:51:11PM +0100, Csaba Nagy wrote:
But it's still too cumbersome. How about creating a new operator? With
the example below the query would simply be

SELECT s_host ||~ s_post_dir ||~ s_street ||~ s_suffix
FROM parcels
WHERE s_pin = '1201703303520';

alvherre=# CREATE FUNCTION text_concat_nulls_with_an_embedded_space(text,text ) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULLTHEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql;
CREATE FUNCTION
alvherre=# SELECT text_concat_nulls_with_an_embedded_space('foo', NULL);
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space(NULL, 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space('bar', 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
bar foo
(1 fila)
alvherre=# CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR
alvherre=# SELECT 'hi' ||~ 'foo' ||~ null ||~ null ||~ 'bar' ||~ 'baz' ||~ null ||~ 'bye';
?column?
--------------------
hi foo bar baz bye
(1 fila)


--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The first of April is the day we remember what we are
the other 364 days of the year" (Mark Twain)





---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org