concatenation operator fails with null varchars?
От | Shawn T. Rutledge |
---|---|
Тема | concatenation operator fails with null varchars? |
Дата | |
Msg-id | 37DDF83D.8BAFCED9@bigfoot.com обсуждение исходный текст |
Список | 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@bigfoot.com finger rutledge@cx47646-a.phnx1.az.home.com __) | | \__________________________________________________________________
В списке pgsql-general по дате отправления: