Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.1) with ESMTP id NAA65039 for ; Tue, 20 Apr 1999 13:21:23 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA14255; Tue, 20 Apr 1999 13:20:41 -0400 (EDT) To: Nuchanard Chiannilkulchai cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] null values to be replaced by a default value In-reply-to: Your message of Tue, 20 Apr 1999 17:31:41 +0200 <371C9DDD.AC4ED97B@valigene.com> Date: Tue, 20 Apr 1999 13:20:40 -0400 Message-ID: <14253.924628840@sss.pgh.pa.us> From: Tom Lane Nuchanard Chiannilkulchai writes: > To return one value not null, i've created a function : You can't really do that with functions right now --- any null input to a function causes the result to be null. (There's been discussion on the hackers list about improving this, but it won't happen before 6.6 at the earliest.) Fortunately, the standard COALESCE operator does what you want already: COALESCE(a,b) produces the result you were looking for, and you won't need to make a new copy for every data type either. Basically, COALESCE takes any number of arguments and returns the first one that's not NULL. It's a shorthand for a CASE expression, (CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ...) regards, tom lane