Re: NULLs ;-)

On þri, 2006-11-28 at 09:42 -0500, John D. Burger wrote:
> Scott Ribe wrote:
>
> > where a <> b or (a is null and b is not null) or (a is not null and
> > b is null)
>
> In the absence of IS DISTINCT FROM, I think this has the same semantics:
>
>    where coalesce(a, b) <> coalesce(b, a)

sorry, but no.

test=# create table logic (a int, b int);
CREATE TABLE
test=# insert into logic values (null,null);
INSERT 34495399 1
test=# insert into logic values (null,1);
INSERT 34495400 1
test=# insert into logic values (1,null);
INSERT 34495401 1
test=# insert into logic values (1,1);
INSERT 34495402 1
test=# select a,b,
              coalesce(a, b) <> coalesce(b, a) as coal,
              a IS DISTINCT FROM b as dist from logic;
 a | b | coal | dist
---+---+------+------
   |   |      | f
   | 1 | f    | t
 1 |   | f    | t
 1 | 1 | f    | f
(4 rows)

test=#


gnari