Received: from chac.qld.edu.au (linux.chac.qld.edu.au [203.37.221.131] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id RAA10005 for ; Thu, 30 Mar 2000 17:59:51 -0500 (EST) (envelope-from jhunt@chac.qld.edu.au) Received: from chac.qld.edu.au (chac.inet [203.37.221.129]) by chac.qld.edu.au (8.9.3/8.9.3) with ESMTP id IAA32603; Fri, 31 Mar 2000 08:50:31 +1000 (EST) (envelope-from jhunt@chac.qld.edu.au) Message-ID: <38E3DC17.3F1A865@chac.qld.edu.au> Date: Fri, 31 Mar 2000 08:58:32 +1000 From: Julie Hunt Reply-To: jhunt@chac.qld.edu.au Organization: Cannon Hill Anglican College X-Mailer: Mozilla 4.61 (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Joseph Shraibman CC: "pgsql-sql@postgresql.org" Subject: Re: isnull References: <004701bf9901$c2b322c0$9714a8c0@julies> <38E3D461.E80EB898@selectacast.net> Content-Type: text/plain; charset=us-ascii; x-mac-type="54455854"; x-mac-creator="4D4F5353" Content-Transfer-Encoding: 7bit X-Archive-Number: 200003/6 Yes, you're right. I remember attempting to do an outer left join using the nullif but without success so I used the case function instead with success. Maybe someone else knows more about nullif and why it doesn't seem to work. Regards, Julie Joseph Shraibman wrote: > Julie Hunt wrote: > > > Sorry, I haven't come across ifnull > > Ah, I think the documentation that came with 6.5.3 was wrong, because > http://www.postgresql.org/docs/postgres/functions.htm#AEN2257 has the function > nullif where ifnull was in my documentation. I don't get errors now, but it > just doesn't seem to do it's job. > > playpen=> insert into tablea values (100, null); > INSERT 23168 1 > playpen=> select * from tablea; > indexa|data > ------+---- > 1| 1 > 2| 2 > 3| 2 > 4| 4 > 7| 7 > 6| 8 > 5| 8 > 100| > (8 rows) > > playpen=> select *, data from tablea; > indexa|data|data > ------+----+---- > 1| 1| 1 > 2| 2| 2 > 3| 2| 2 > 4| 4| 4 > 7| 7| 7 > 6| 8| 8 > 5| 8| 8 > 100| | > (8 rows) > > playpen=> select *, nullif(data, 9) from tablea; > indexa|data|case > ------+----+---- > 1| 1| 1 > 2| 2| 2 > 3| 2| 2 > 4| 4| 4 > 7| 7| 7 > 6| 8| 8 > 5| 8| 8 > 100| | > (8 rows) > > > > > Julie > > > > >Oops, my mistake. I meant ifnull. Whenever I try to use ifnull I get an > > >error so I have to use a case when a = null then default else a end. > > > > > >Julie Hunt wrote: > > > > > >> There is a space between is and null: is null > > >> > > >> Regards > > >> > > >> Joseph Shraibman wrote: > > >> > > >> > Could someone give me an example of isnull? Whenever I try it I get an > > >> > error message. > > >