Check is not null in sql?

Question

PostgreSQL 8.4

I have the following piece of sql-script:

WHERE p.partner_id IS NOT NULL
    AND (let.external_transaction_registration_date IS NULL OR
 let.external_transaction_registration_date > :date)

Is it the correct way to check for either not-null or if null then there're no constratints should be applied? Does postgresql evalute an exression E2 in (E1 OR E2) even if E1 is true?

Answer 1

Does postgresql evalute an exression E2 in (E1 OR E2) (even if E1 is true)?

Maybe. It is unpredictable per spec:

The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order.

Furthermore, if the result of an expression can be determined by evaluating only some parts of it, then other subexpressions might not be evaluated at all.

But your expression is safe, because:

SQL uses a three-valued logic system with true, false, and null, which represents "unknown".

In case of NULL values, evaluation goes as (TRUE OR NULL), which is TRUE.

In fact both of these expressions are satisfying your needs (either not-null or if null then there're no constratints should be applied):

WHERE date_col IS NULL OR date_col > :date
-- vs:
WHERE date_col > :date OR date_col IS NULL

Boolean expressions (AND/OR/NOT combinations) in those clauses can be reorganized in any manner allowed by the laws of Boolean algebra.

This also applies to PostgreSQL 8.4.