concatenation operator fails with null varchars?

From: "Shawn T(dot) Rutledge" <ecloud(at)bigfoot(dot)com>
To: pgsql-questions(at)postgresql(dot)org
Subject: concatenation operator fails with null varchars?
Date: 1999-09-14 07:24:45
Message-ID: 37DDF83D.8BAFCED9@bigfoot.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Observe if you will:

contacts=> select * from person;
lastname|othernames|suffixes|creation |entity_id
--------+----------+--------+----------------------+---------
Clinton |Bill | |1999-09-07 18:39:06-07| 28
|Bob | |1999-09-13 23:43:16-07| 33
Rutledge|Shawn | |1999-09-13 00:51:28-07| 1
(3 rows)

contacts=> select * from company;
name |creation |entity_id
-----------------------+----------------------+---------
Alcoa |1999-09-03 18:41:32-07| 25
Peterbilt |1999-09-03 18:41:53-07| 26
US Government |1999-09-03 18:42:07-07| 27
Acme Manufacturing Inc.|1999-09-12 16:28:31-07| 30
ExpressBill |1999-09-12 19:37:47-07| 31
(5 rows)

contacts=> select othernames || ' ' || lastname || ' ' || suffixes as
name, entity_id from person union select name, entity_id from company;
name |entity_id
-----------------------+---------
Acme Manufacturing Inc.| 30
Alcoa | 25
Bill Clinton | 28
ExpressBill | 31
Peterbilt | 26
US Government | 27
| 1
| 33
(8 rows)

contacts=> select othernames || ' ' || lastname as name, entity_id from
person union select name, entity_id from company;

name |entity_id
-----------------------+---------
Acme Manufacturing Inc.| 30
Alcoa | 25
Bill Clinton | 28
ExpressBill | 31
Peterbilt | 26
Shawn Rutledge | 1
US Government | 27
| 33
(8 rows)

It would appear that when using a sequence of concatenation operators,
if
any of the selected varchars are null, then the entire concatenation
fails.
(Note that in the second union, "Shawn Rutledge" showed up despite not
having a "suffixes" value, but "Bob" did not show up because his entry
was missing the required "lastname". I can only assume that "Bill
Clinton"'s
suffixes field is blank rather than null.)

How can I fix this? I'd like the null info to be left out but the rest
of the operators to work.
--
_______
http://www.bigfoot.com/~ecloud
(_ | |_) ecloud(at)bigfoot(dot)com finger
rutledge(at)cx47646-a(dot)phnx1(dot)az(dot)home(dot)com
__) | |
\__________________________________________________________________

Browse pgsql-general by date

  From Date Subject
Next Message amy cheng 1999-09-14 07:51:10 shutdown gracefully & single user mode?
Previous Message amy cheng 1999-09-14 06:35:20 Re: [GENERAL] two phase commit?