NULLs ;-)

On Nov 28, 2006, at 9:37 , Scott Ribe wrote:

(Can we talk about NULL next? :P)

Seriously though, there is one thing I've been meaning to bring up. I
understand why NULLs compare the way they do in queries, and that's
fine.
But there are times when I need to query what would be described in
relational terms as "not known to be equal", and

where a <> b or (a is null and b is not null) or (a is not null
and b is
null)

So, first, have I missed some way to express that more easily in
PG? And if
not, is there any reason not to request a new operator? (Perhaps "a
nktbe
b"? The C guy in me prefers "a != b" but that would be *FAR* too
prone to
confusion with <>.)

Check out IS DISTINCT FROM

http://www.postgresql.org/docs/current/interactive/functions-
comparison.html

I think that will help you.

Michael Glaesemann
grzm seespotcode net

Scott Ribe <scott_ribe@killerbytes.com> writes:

But there are times when I need to query what would be described in
relational terms as "not known to be equal", and

where a <> b or (a is null and b is not null) or (a is not null and b is
null)

IS DISTINCT FROM ?

regards, tom lane

On Mon, 2006-11-27 at 17:37 -0700, Scott Ribe wrote:

(Can we talk about NULL next? :P)

Oh bring it on! :)

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Check out IS DISTINCT FROM

http://www.postgresql.org/docs/current/interactive/functions-
comparison.html

I think that will help you.

It's exactly what I was asking for.

Well, except for the deja-vu feeling where I'm wondering if I didn't read
that a long time ago and then forget it when I needed it. I didn't ask for
that ;-)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

--- Scott Ribe <scott_ribe@killerbytes.com> wrote:

(Can we talk about NULL next? :P)

Seriously though, there is one thing I've been meaning to bring up. I
understand why NULLs compare the way they do in queries, and that's fine.
But there are times when I need to query what would be described in
relational terms as "not known to be equal", and

where a <> b or (a is null and b is not null) or (a is not null and b is
null)

is rather clumsy and verbose (though precise), especially when it needs to
be combined with other criteria.

So, first, have I missed some way to express that more easily in PG? And if
not, is there any reason not to request a new operator? (Perhaps "a nktbe
b"? The C guy in me prefers "a != b" but that would be *FAR* too prone to
confusion with <>.)

how about

SELECT *
FROM
YOURTABLE
where
( a = b ) IN UNKNOWN;

how about

SELECT *
FROM
YOURTABLE
where
( a = b ) IN UNKNOWN;

Well, actually, it would be:
a = b or (a = b) is unknown
But that certainly would more concise.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:

Well, actually, it would be:
a = b or (a = b) is unknown
But that certainly would more concise.

But

regression=# select (null = null) is unknown;
?column?
----------
t
(1 row)

which I think is not what you wanted.

regards, tom lane

But

regression=# select (null = null) is unknown;
?column?
----------
t
(1 row)

which I think is not what you wanted.

Right.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

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)

although it's not as concise as one might wish.

- John D. Burger
MITRE

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

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.

Argh, my expression is just nonsense - I was thinking of something like:

coalesce(a, 'SOME MAGIC VALUE') <> coalesce(b, 'SOME MAGIC VALUE')

and wanted to make it work for any types. Sigh.

- John D. Burger
MITRE

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.

So it would have to be where coalesce(a, b, 0) <> coalesce(b, a, 0) for your
example with ints, and likewise some default value for other column types...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On �ri, 2006-11-28 at 12:28 -0700, 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.

So it would have to be where coalesce(a, b, 0) <> coalesce(b, a, 0) for your
example with ints, and likewise some default value for other column types...

no cigar.

test=# select a,b,
coalesce(a, b, 0) <> coalesce(b, a, 0) as john,
a IS DISTINCT FROM b as dist
from logic;
a | b | john | dist
---+---+------+------
| | f | f
| 1 | f | t
1 | | f | t
1 | 1 | f | f
(4 rows)

gnari

no cigar.

Well, duh. Showing why IS DISTINCT FROM is useful.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice