lower() not working correctly...?
I have the following query:
select lower(firstname) || ' ' || lower(lastname) from person
firstname and lastname are VARCHAR
lower() returns NULL when firstname OR lastname is NULL, is this correct?
This is 8.2devel from 24.08.2006.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
This works as intended. Try this:
select coalesce(lower(firstname), '') || ' ' || coalesce(lower(lastname), '') from person
Concating something unknown (=NULL) and a string = unknown (=NULL)
-----Ursprüngliche Nachricht-----
Von: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] Im Auftrag von Andreas Joseph Krogh
Gesendet: Freitag, 15. September 2006 09:53
An: pgsql-hackers@postgresql.org
Betreff: [HACKERS] lower() not working correctly...?
I have the following query:
select lower(firstname) || ' ' || lower(lastname) from person
firstname and lastname are VARCHAR
lower() returns NULL when firstname OR lastname is NULL, is this correct?
On f�s, 2006-09-15 at 09:52 +0200, Andreas Joseph Krogh wrote:
I have the following query:
select lower(firstname) || ' ' || lower(lastname) from person
firstname and lastname are VARCHAR
lower() returns NULL when firstname OR lastname is NULL, is this correct?
In fact, your problem has nothing to do with lower().
You probably did not expect the || operator
to return NULL when any of the operands is NULL
as seen in
select firstname || ' ' || lastname from person
so , as someone else has already mentioned,
you should use coalesce.
gnari