PostgreSQL and NULL

If activity_gmt for a particular row is null is NULL does an SQL query activity_gmt>='2012-03-01' retrieve it?      It will not be included, see answer from Sierra Listserv:

It won’t be included.  I’ll explain because how NULL works in Boolean expression is counter-intuitive at first.

Mostly commonly NULL means “missing value” or “no value at all”.  But in SQL NULL means “could be any value at all, but that value is unknown to you”.

If I was to ask you “I’m thinking of some date and you don’t know what that date is. Now tell me if that date is >= 2012-03-01.”  Your answer is going to be “I don’t know. How could I possibly know?”

In SQL that same conversation would be like this.

[sierra-db iii] psql> SELECT NULL >= DATE '2012-03-01';

+----------+

| ?column? |

+----------+

| (null)   |

+----------+

In a WHERE clause of a SELECT statement, you want to know if your Boolean expression is true and the row will be included, or false and the row won’t be included.

So the next question is “Is NULL = TRUE?”. Read this as “Is some Boolean value that is unknown to you equal to true”. The answer obviously is (except to Microsoft) “I don’t know. How could I possible know?”.  That is, the answer is NULL.

[sierra-db iii] psql> SELECT NULL = TRUE;

+----------+

| ?column? |

+----------+

| (null)   |

+----------+

It turns out — and this is as much a surprise to programmers as it is to librarians, and where Microsoft went wrong with Access and SQL Server — that in SQL there is not just TRUE and FALSE, but TRUE, FALSE and NULL. That’s right, in SQL NULL is actually a third Boolean state, not a distinct type or value. Booleans in SQL are like those check boxes you see sometimes in a dialog box where the checkbox can be clear (FALSE), ticked (TRUE) or grey (NULL).

So actually in a WHERE clause you need to know about third NULL state.  In ANSI-standard SQL, a WHERE clause that calculates as NULL for a row will excluded that row.

BTW Is NULL = NULL?  Microsoft says it is, and most programming languages will say is.  ANSI-standard SQL says “You’re asking me if 2 values unknown to me are equal!? How could I possibly know!? NULL.”

Regards,

Jim

JIM NICHOLLS | Library Technician

------------------------------

Hi,

If I write an SQL query like activity_gmt>='2012-03-01' and activity_gmt for a particular row is null, will that record get returned or not? On Sybase, it would not be returned but I want to make sure the same is true in PostgreSQL.  I looked up the documentation and am leaning toward it not being returned but I'd like to make sure. (http://www.postgresql.org/docs/9.1/static/functions-comparison.html).  For now to be on the safe side,  I'll write the queries like (activity_gmt IS NOT NULL AND activity_gmt>='2012-03-01') but I'd like to know how essential this is.

Thoughts?

Thanks,

Gem Stone-Logan
IT Application Engineer
High Plains Library District