What's wrong with this query?

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

Responses

Browse pgsql-general by date

  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)