SQL equality/inequality comparison with nullable values

Question

first take, kludge solution, sentinel approach(it's imperative that your program should not allow inputting of sentinel value):

 select coalesce(a, -2147483648) = coalesce(b, -2147483648) as is_equal -- a little postgresism

let's say you forgot to block the sentinel value on your program, the user inputted -2147483648 on the B field, and A is null. the code above reports true, should report false, should not report true nor null.

what's the most concise way to compare equality on nullable fields? A == B should just report either true or false only, regardless if the field(s) are nullable or not.

Answer 1

The operator <=> does what you want in other database engines; is it not in postgres? Otherwise, a is null and b is null or (a is not null and b is not null and a == b) should work. This works because FALSE AND NULL is FALSE.

Answer 2

Probably IS [NOT] DISTINCT FROM will help here.