X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 16A379FB1E0 for ; Fri, 15 Sep 2006 07:04:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 44910-08 for ; Fri, 15 Sep 2006 07:04:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx3.ipf.is (man.ipf.is [85.197.192.163]) by postgresql.org (Postfix) with ESMTP id 578D29F9ECF for ; Fri, 15 Sep 2006 07:04:27 -0300 (ADT) Received: (qmail 29409 invoked by uid 1010); 15 Sep 2006 10:03:25 +0000 Received: from 88.149.106.19 by man (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.3/4.5):. Processed in 0.432418 secs); 15 Sep 2006 10:03:25 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via man X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.3/4.5):. Processed in 0.432418 secs Process 29402) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx3.ipf.is with SMTP; 15 Sep 2006 10:03:25 +0000 Subject: Re: [HACKERS] lower() not working correctly...? From: Ragnar To: Andreas Joseph Krogh Cc: pgsql-sql@postgresql.org In-Reply-To: <200609150952.53956.andreak@officenet.no> References: <200609150952.53956.andreak@officenet.no> Content-Type: text/plain; charset=ISO-8859-1 Date: Fri, 15 Sep 2006 10:07:05 +0000 Message-Id: <1158314825.17224.190.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.195 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200609/110 X-Sequence-Number: 26321 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