Re: "IS NOT NULL" != "NOT NULL"

Sean Chittenden <sean(at)chittenden(dot)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