How is this possible?
select count(*) from happiness where source = 'love';
-[ RECORD 1 ]
count | 0
select count(*) from happiness where source != 'love';
-[ RECORD 1 ]
count | 9279
select count(*) from happiness;
-[ RECORD 1 ]---
count | 1418962...
And the \d
source | character varying(255) |
I'm going to punch myself in the face really hard when I get the answer to this one.
SQL uses a so-called "three-valued" logic, whereby a given boolean expression can be either "true", "false", or "null". A query of this form:
select count(*) from happiness where ...;
will only return records where ... is "true", skipping the records where it's either "false" or "null".
When source is null, source = 'love' and source != 'love' are both null; so both versions of your queries will skip any records where source is null. (NOT (source = 'love') will also be null: not "true" is "false", and not "false" is "true", but not null is still null.)
The reason for this behavior is that null represents "unknown"; if source is null, then there's no way to know whether it's "really" supposed to be 'love', or "really" supposed to be something else. (I think we tend to think of null as a distinct value, different from all others, but that's not its intended meaning.)
What you probably want is:
select count(*) from happiness where source is null or source != 'love';
select count(*) from happiness where coalesce(source, '') != 'love';
Maybe do it:
select count(*) from happiness where source is null;
if source is null then source is not equal to love but also source is not different to love
