pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: isnull
6+ messages / 4 participants
[nested] [flat]
* Re: isnull
@ 2000-03-30 22:25 Joseph Shraibman <jks@p1.selectacast.net>
0 siblings, 2 replies; 6+ messages in thread
From: Joseph Shraibman @ 2000-03-30 22:25 UTC (permalink / raw)
To: Julie Hunt <jhunt@chac.qld.edu.au>; pgsql-sql
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.
> >
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: isnull
@ 2000-03-30 22:58 Julie Hunt <jhunt@chac.qld.edu.au>
parent: Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 1 reply; 6+ messages in thread
From: Julie Hunt @ 2000-03-30 22:58 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: pgsql-sql
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.
> > >
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: isnull
@ 2000-03-30 23:07 Joseph Shraibman <jks@p1.selectacast.net>
parent: Julie Hunt <jhunt@chac.qld.edu.au>
0 siblings, 2 replies; 6+ messages in thread
From: Joseph Shraibman @ 2000-03-30 23:07 UTC (permalink / raw)
To: jhunt@chac.qld.edu.au; +Cc: Joseph Shraibman <jks@p1.selectacast.net>; pgsql-sql
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')
>
> 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.
> > > >
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: isnull
@ 2000-03-31 00:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2000-03-31 00:11 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: Julie Hunt <jhunt@chac.qld.edu.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: isnull
@ 2000-03-31 01:10 Julie Hunt <jhunt@chac.qld.edu.au>
parent: Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 0 replies; 6+ messages in thread
From: Julie Hunt @ 2000-03-31 01:10 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: pgsql-sql
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')
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: isnull
@ 2000-03-31 01:43 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joseph Shraibman <jks@p1.selectacast.net>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2000-03-31 01:43 UTC (permalink / raw)
To: Joseph Shraibman <jks@p1.selectacast.net>; +Cc: pgsql-sql
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2000-03-31 01:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-30 22:25 Re: isnull Joseph Shraibman <jks@p1.selectacast.net>
2000-03-30 22:58 ` Julie Hunt <jhunt@chac.qld.edu.au>
2000-03-30 23:07 ` Joseph Shraibman <jks@p1.selectacast.net>
2000-03-31 01:10 ` Julie Hunt <jhunt@chac.qld.edu.au>
2000-03-31 01:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-03-31 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox