Search code examples
postgresqlnulllogical-operatorscomparison-operators

Postgresql NOT NULL AND <> '' vs NOT NULL OR <> ''

I have read lots about the difference between NOT NULL vs <>''

The best explanation I found for it was in:

https://www.postgresql.org/message-id/AANLkTilEsUTIeUkZCX9Vc14kciFiuvFBRRE-yen4K_Zi@mail.gmail.com which states:

NULL means the question hasn't been answered yet, thus there is no answer, there may be an answer once it is provided so you can't really say the answer is empty. Empty would mean the answer has been given and the answer is empty.

For the table that I am working on, I was trying to filter results for valid postcodes only (postcode is a VARCHAR in this specific table), I tried the following:

SELECT postcode FROM customer_table_1 
WHERE postcode IS NOT NULL OR postcode <> '';

However this gives some blank postcodes in my results. Breaking this down ...

SELECT postcode FROM customer_table_1 
WHERE postcode IS NOT NULL;

gives some blank postcodes whereas 

SELECT postcode FROM customer_table_1 
WHERE postcode <>'';

only gives valid postcodes in the result. Therefore the IS NOT NULL part of the query isn't doing what I thought it was.

As part of a more complicated query, I have previously used:

SELECT postcode FROM customer_table_1 
WHERE postcode IS NOT NULL AND postcode <> '';

and have achieved the desired result. However I have always thought it should be 

SELECT postcode FROM customer_table_1 
WHERE postcode IS NOT NULL OR postcode <> '';

Because I am looking for all records that have a valid postcode (i.e. are NOT NULL or NOT empty strings) should these not be connected with an OR statement rather than an AND? I'm not looking for postcodes that are both NULL and empty strings, just one or the other.

Apologies if this is a stupid question, using an AND doesn't seem logical to me and I don't want to blindly do something without understanding the process behind the result. I thought I fully understood the difference between NOT NULL and <>'' but as with most things Postgres related, the more I delve into it the more I realize I don't actually know!

Solution

  • You would benefit from knowing De Morgan's Law which is fundamental to Boolean logic.

    In this case, your condition is not what you expressed as:

    (NOT NULL) OR (NOT EMPTY)

    but is actually:

    NOT (NULL OR EMPTY)

    which expanded out according to De Morgan actually becomes:

    (NOT NULL) AND (NOT EMPTY)

    hence why AND is the correct (and indeed "logical") operator.