isnull() or is it?

I thought that isnull() was something that existed in PostgreSQL 6.4, am
I wrong or is it implemented differently than Sybase? Or am I not making
sense (it should be easy to explain if I'm not)?

isnull() is on our TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Right now I'm trying to do something that 'Practical SQL' seems to
think is normal yet brings up the questions from earlier
conversations. I'm querying two tables where
if the second table (specialprice) has nothing that matches sku, I
don't care.

From your example query, it seems that you want to substitute 0.0 for

the "specialprice" field if an entry corresponding to a row in the first
table does not exist. That matches up with the capabilities of a left
outer join, where you would use CASE or COALESCE() to substitute the
zero for a null. Postgres does not have outer joins yet.

Your example does *not* give you the effect you want, since the inner
join you are specifying will not match up non-existent rows. btw,
neither of my reference/tutorial books mentions ifnull() (or at least
they don't have an index entry for it). I'm looking at "A Guide to the
SQL Standard", Date and Darwen, and "Understanding the New SQL", Melton
and Simon.

In the meantime I think you can mimic the effect using a union:

select products.image, products.sizes, products.colors,
products.weight, products.category, products.accessories,
products.saleprice, products.ourcost, products.description,
specialprice.specialprice
from products,specialprice
where (products.sku = '28434')
and (products.sku = specialprice.sku)
union
select products.image, products.sizes, products.colors,
products.weight, products.category, products.accessories,
products.saleprice, products.ourcost, products.description,
0.0
from products
where products.sku = '28434'
and (products.sku not in (select sku from specialprice);

That last clause could just have the constant rather than products.sku.

Postgres does have ISNULL in the parser, but that is just equivalent to
IS NULL.

Good luck.

- Tom

isnull() is on our TODO list.

If the one-line description in the TODO list is accurate and complete,
istm that the SQL92-standard function COALESCE() which I put into the
current development tree as part of the CASE feature does that and more.

Neither function would allow an inner join to magically become an outer
join. Speaking of which, remember that you're planning on helping with
outer joins for the next release, right? Or at least you haven't turned
me down yet... :)

- Tom

isnull() is on our TODO list.

If the one-line description in the TODO list is accurate and complete,
istm that the SQL92-standard function COALESCE() which I put into the
current development tree as part of the CASE feature does that and more.

Neither function would allow an inner join to magically become an outer
join. Speaking of which, remember that you're planning on helping with
outer joins for the next release, right? Or at least you haven't turned
me down yet... :)

I reserve the right to fail, though.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in Oracle and
Informix.
isnull() would suggest a boolean return value to me.

Andreas

Zeugswetter Andreas IZ5 wrote:

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in

Oracle and

Informix.
isnull() would suggest a boolean return value to me.

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Yes, the difference is only the name of the function.

Andreas

Zeugswetter Andreas IZ5 wrote:

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in Oracle and
Informix.
isnull() would suggest a boolean return value to me.

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Example:

SELECT reference, name, IsNull(price, 0.00)
FROM t_items

reference name price
------------ --------------- -------
W95 Windows95 190.00
WNT400 Windows NT 4.0 490.00
LX Linux 0.00

rather than:

SELECT reference, name, price,
FROM t_items

reference name price
------------ --------------- -------
W95 Windows95 190.00
WNT400 Windows NT 4.0 490.00
LX Linux NULL

--
H.Lefebvre

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in Oracle and
Informix.
isnull() would suggest a boolean return value to me.

Andreas

NVL added to TODO.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Zeugswetter Andreas IZ5 wrote:

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in

Oracle and

Informix.
isnull() would suggest a boolean return value to me.

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Yes, the difference is only the name of the function.

isnull() is used by Ingres.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Zeugswetter Andreas IZ5 wrote:

isnull() is on our TODO list.

It should be called NVL() from "Null VaLue" like in Oracle and
Informix.
isnull() would suggest a boolean return value to me.

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

We should support both isnull() and nvl(). On the TODO list now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.

- Tom

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.

Oh, NVL and isnull are not standard? Then let's just use coalesce. I
will remove them from the TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

On Wed, 9 Dec 1998, Bruce Momjian wrote:

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.

Oh, NVL and isnull are not standard? Then let's just use coalesce. I
will remove them from the TODO list.

Surprises me too. I was under the (incorrect) impression that isnull
was standard.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"There is no outfit less entitled to lecture me about bloat
than the federal government" -- Tony Snow
==========================================================================

Surprises me too. I was under the (incorrect) impression that isnull
was standard.

If it is, my books don't bother mentioning it. It would be pretty easy
to implement, if it *is* standard, since it is just a subset of
COALESCE() behavior.

But I'm a bit worried about the recent trend toward coding in
non-standard extensions as *alternatives* to standards-based behavior
just because a specific product has it. I know it's convenient for some,
but there are *lots* of products which have these kinds of things, each
in a different way, and at some point we would collapse under the weight
of trying to be all things at once.

Not naming any names, but *$ products seem to be the worst violators of
the simplest standards. One might ascribe this to simple incompetence,
except for their no-longer-in-doubt agenda of subverting standards in a
so-far-successful drive for market dominance. For example, Access
produces code of the form
val = NULL
as an equivalent to the SQL92-standard
val IS NULL

Where my Date book has a sentence:
"It is not possible to specify NULL explicitly as an operand of a
conditional expression - e.g., 'WHERE X = NULL' is illegal."

We now have a parser which violates that, for the sake of Access
compatibility. Of course, we do support another extension to SQL92 which
violates another rule in the same area:
"It is not possible to specify NULL explicitly as a select-item -- e.g.,
'SELECT NULL' is illegal."

Oh well. otoh, perhaps some of this would be legal for SQL3, which has a
somewhat more developed concept of NULL...

- Tom

If it is interesting to someone, we can partially emulate COALESCE
right now as:

create function coalesce(integer) returns integer as
'declare
nonullo alias for $1;
begin
if nonullo then
return nonullo;
else
return 0;
end if;
end;
' language 'plpgsql';
CREATE

select *,coalesce(comm) from emp where comm is null;

ename|empno|job | hiredate|sal |comm|deptno|level| mgr|coalesce
-----+-----+----------+----------+---------+----+------+-----+----+--------
BLAKE| 7698|MANAGER |1981-05-01|$2,850.00| | 30| 3|7782| 0
JONES| 7900|CLERK |1981-12-03|$950.00 | | 30| 2|7782| 0
CLARK| 7844|SALESMAN |1981-09-08|$1,500.00| | 10| 2|7839| 0
(3 rows)

-Jose'-

With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.

Oh, NVL and isnull are not standard? Then let's just use coalesce. I
will remove them from the TODO list.

VV> Surprises me too. I was under the (incorrect) impression that isnull
VV> was standard.

On a somewhat related topic, the development parser also now supports
the SQL92-standard NULLIF(), which behaves in an opposite sense to the
common IsNull() under discussion. That is, it tests the first argument
against the second, and then returns NULL if they are equal. Weird.

- Tom

If it is interesting to someone, we can partially emulate COALESCE
right now as:

create function coalesce(integer) returns integer as
'declare
nonullo alias for $1;
begin
if nonullo then
return nonullo;
else
return 0;
end if;
end;
' language 'plpgsql';
CREATE

Pardon, but you still misuse the fact, that PL/pgSQL's IF
expression is implicitly casted into a boolean. That's only
possible for integer values.

Please use

IF nonullo ISNULL THEN
RETURN 0;
ELSE
RETURN nonullo;
END IF;

instead, because this would work for other types (like text,
varchar etc.) too.

Since PL functions can be overloaded (like SQL functions), it
would be possible, but currently not that performant :-(, to
create such a function for all types required.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #