Re: "IS NOT NULL" != "NOT NULL" - Mailing list pgsql-general

Sean Chittenden <sean@chittenden.org> writes:
> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
>  count
> -------
>      0
> (1 row)


> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
>  count
> -------
>    1242
> (1 row)

> Why aren't those the same?  Seems like the IS would be an extra word
> that's not necessarily needed.  ::shrug::  -sc

This is more than a tad hard to believe, considering that the parser
converts both of these constructs into the same internal representation:

        | a_expr NOTNULL
                {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
        | a_expr IS NOT NULL_P
                {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }

Can you provide a reproducible example where the results are different?

            regards, tom lane