X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CFCD23A4A78 for ; Fri, 19 Nov 2004 17:34:06 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90174-07 for ; Fri, 19 Nov 2004 17:33:57 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id B11A33A4B1E for ; Fri, 19 Nov 2004 17:33:57 +0000 (GMT) Received: (qmail 26637 invoked by uid 500); 19 Nov 2004 17:45:43 -0000 Date: Fri, 19 Nov 2004 11:45:43 -0600 From: Bruno Wolff III To: Don Drake Cc: pgsql-sql@postgresql.org Subject: Re: NULLS and string concatenation Message-ID: <20041119174543.GA26505@wolff.to> Mail-Followup-To: Don Drake , pgsql-sql@postgresql.org References: <6c21003b0411190912630d4afe@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6c21003b0411190912630d4afe@mail.gmail.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/201 X-Sequence-Number: 19520 On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake wrote: > I have a function that uses an execute statement to insert data into a > table, I do in my implementation of table partitioning. > > Anyway, I ran into trouble when NULL values were being passed in > (fields are nullable) and my insert statement turned into a big NULL. > > Here's an equivalent statement that caused trouble: > > select 'some text, should be null:'|| NULL > > This returns NULL and no other text. Why is that? I wasn't expecting > the "some text.." to disappear altogether. > > Is this a bug? No. > > I was able to work around the problem by using COALESCE (and casting > variables since it wants the same data types passed to it). This is what you should do. > > -Don > > -- > Donald Drake > President > Drake Consulting > http://www.drakeconsult.com/ > 312-560-1574 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly