Search Postgresql Archives

Re: What's wrong with this query?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Jun 21, 2009, at 3:37 PM, Thomas Kellerer wrote:

Mike Christensen wrote on 22.06.2009 00:10:
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.
That is standard behaviour.
A comparison with a NULL value always returns false (and that is not a Postgres speciality).

You need to use

select *
from test
where value != '00000000-0000-0000-0000-000000000000'
or value is null;

Yup.

Or where value is distinct from '00000000-0000-0000-0000-000000000000';

Cheers,
  Steve


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux