Thread: difference between not null and <> null

sarlav kumar <sarlavk@yahoo.com> writes:
> Is there a difference between the usages of NOT NULL and <> NULL??

They are not even remotely the same thing.  = NULL and <> NULL are never
correct (they both always yield NULL).  You want IS NULL or IS NOT NULL
instead.

> Do the following in anyway affect the use of index scan?
> 1) use of NOT NULL
> 2) use of now() instead of the actual date

NULL tests aren't currently indexable (maybe someday they will be), but
if you really need it, you can fake it by creating a partial index over
just the null or nonnull rows.

"timestampcol = now()" is fine, but something like
"timestampcol > now() - interval '1 day'" usually won't get indexed,
because the planner thinks it will scan too much of the table.

See the pgsql-performance archives for more discussion and workarounds.

            regards, tom lane