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