From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | What's wrong with this query? |
Date: | 2009-06-21 22:10:04 |
Message-ID: | 7aa638e00906211510o62ec3cd7ide126d24ca0f194@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I just tracked down a bug in my software due to an "unexpected" behavior in
Postgres.. Can someone clarify why this doesn't work (I haven't tried it on
MSSQL or anything else, so I'm not sure if this is the official SQL standard
or anything)..
CREATE TABLE test
(
value uuid
);
INSERT INTO test VALUES ('00000000-0000-0000-0000-000000000000');
INSERT INTO test VALUES ('11111111-1111-1111-1111-111111111111');
INSERT INTO test VALUES (null);
select * from test where value != '00000000-0000-0000-0000-000000000000';
What I expect to get is two rows: the '11111111-1111-1111-1111-111111111111'
row and the null row, as both those values are in fact not
'00000000-0000-0000-0000-000000000000'. However, I only get the first one.
I can change my query to:
select * from test where value is null or value !=
'00000000-0000-0000-0000-000000000000';
and that will give me the null rows, or rows that don't match that UUID. Is
there a better way of writing this query? Thanks!
Mike
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2009-06-21 22:37:41 | Re: What's wrong with this query? |
Previous Message | Ivan Sergio Borgonovo | 2009-06-21 20:11:59 | Re: cursors, temp tables, dynamic sql technique (was and is: looping over a small record set over and over in a function) |