Обсуждение: Re: isnull
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. > >
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. > > >
Julie Hunt wrote: > 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 > I've actually taken a closer look at the web page and not only did the name change, but I now notice that they change what it is supposed to do as well. It does work as currently defined, but I needed something that does what the original documentation said: IFNULL(input,non-NULL substitute)return second argument if first is NULLExample: IFNULL(c1, 'N/A') > > 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. > > > >
Joseph Shraibman <jks@p1.selectacast.net> writes: > 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. NULLIF doesn't do what you seem to expect: it is for *creating* nulls, not removing them. It produces a null if the two arguments are equal, else the first argument. COALESCE is probably the thing you are after: it produces the first nonnull value among its arguments, reading left to right. There isn't anything called IFNULL in the SQL92 spec, though there is an IS NULL test (note space). No, I didn't choose these names ;-) ... blame the SQL92 authors ... regards, tom lane
Could you describe exactly what you are trying to achieve and the select statement with the case you tried. Joseph Shraibman wrote: > > > I've actually taken a closer look at the web page and not only did the name change, > but I now notice that they change what it is supposed to do as well. It does work > as currently defined, but I needed something that does what the original > documentation said: > > IFNULL(input,non-NULL substitute) > return second argument if first is NULL > Example: IFNULL(c1, 'N/A')
> Julie Hunt wrote: > > > 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 > > > > I've actually taken a closer look at the web page and not only did the name change, > but I now notice that they change what it is supposed to do as well. It does work > as currently defined, but I needed something that does what the original > documentation said: > > IFNULL(input,non-NULL substitute) > return second argument if first is NULL > Example: IFNULL(c1, 'N/A') > New function is COALESCE. It returns the first non-null argument. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026