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
__) | |
\__________________________________________________________________
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? |