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

>>>Tom Lane said:
> 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?

On 7.1.3 the results are:

customer=# select count(*) from croute where archived_at not null;
ERROR: parser: parse error at or near "null"
customer=# select count(*) from croute where archived_at is not null;
count
-------
437
(1 row)

archived_at is timestamp

What Postgres version is this?

By the way,

customer=# select count(*) from croute where archived_at != NULL;
count
-------
0
(1 row)

which may or may not be wrong :-) NULL is thereoretically not possible to be
compared to anything, but...

Daniel