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

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

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