Thread: NULLs ;-)
> (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 <>.) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
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