pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlower() not working correctly...?
7+ messages / 6 participants
[nested] [flat]
* lower() not working correctly...?
@ 2006-09-15 07:52 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 2 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2006-09-15 07:52 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 7+ messages in thread
* lower() not working correctly...?
@ 2006-09-15 08:06 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 2 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2006-09-15 08:06 UTC (permalink / raw)
To: pgsql-sql
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lower() not working correctly...?
@ 2006-09-15 08:21 Mario Weilguni <mario.weilguni@icomedias.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 0 replies; 7+ messages in thread
From: Mario Weilguni @ 2006-09-15 08:21 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-hackers@postgresql.org
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lower() not working correctly...?
@ 2006-09-15 08:44 Guillaume Lelarge <guillaume@lelarge.info>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2006-09-15 08:44 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
Andreas Joseph Krogh a ecrit le 15/09/2006 10:06:
> 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.
>
NULL is an unkown value. lower of an unkown value is unknown... thus
NULL. This is correct behavior.
--
Guillaume.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lower() not working correctly...?
@ 2006-09-15 08:44 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2006-09-15 08:44 UTC (permalink / raw)
To: pgsql-sql
am Fri, dem 15.09.2006, um 10:06:40 +0200 mailte Andreas Joseph Krogh folgendes:
> 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?
Yes. You can user coalesce for this:
select coalesce(lower(firstname),'') || ...
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lower() not working correctly...?
@ 2006-09-15 08:50 Amit_Wadhwa@Dell.com
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 7+ messages in thread
From: Amit_Wadhwa@Dell.com @ 2006-09-15 08:50 UTC (permalink / raw)
To: pgsql-sql; majordomo@postgresql.org
unsubscribe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [HACKERS] lower() not working correctly...?
@ 2006-09-15 10:07 Ragnar <gnari@hive.is>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 0 replies; 7+ messages in thread
From: Ragnar @ 2006-09-15 10:07 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-09-15 10:07 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-15 07:52 lower() not working correctly...? Andreas Joseph Krogh <andreak@officenet.no>
2006-09-15 08:21 ` Mario Weilguni <mario.weilguni@icomedias.com>
2006-09-15 10:07 ` Ragnar <gnari@hive.is>
2006-09-15 08:06 lower() not working correctly...? Andreas Joseph Krogh <andreak@officenet.no>
2006-09-15 08:44 ` Guillaume Lelarge <guillaume@lelarge.info>
2006-09-15 08:50 ` Amit_Wadhwa@Dell.com
2006-09-15 08:44 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox