Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id RAA17975
	for <pgsql-interfaces@postgreSQL.org>;
	Thu, 24 Feb 2000 17:23:51 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA23140;
	Thu, 24 Feb 2000 17:23:47 -0500 (EST)
To: Joseph Shraibman <jks@p1.selectacast.net>
cc: "pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] selectecting not null varchars. 
In-reply-to: <38B58AAE.B28DD6CB@selectacast.net> 
References: <38B58AAE.B28DD6CB@selectacast.net>
Comments: In-reply-to Joseph Shraibman <jks@p1.selectacast.net>
	message dated "Thu, 24 Feb 2000 14:46:54 -0500"
Date: Thu, 24 Feb 2000 17:23:46 -0500
Message-ID: <23137.951431026@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Joseph Shraibman <jks@p1.selectacast.net> writes:
> select * from directory where h != null AND length(h) > 0 ;

The correct, SQL92-approved spelling for this condition is

	WHERE h IS NOT NULL AND ...

We also accept the abbreviation "h NOTNULL", although that's not a
standard AFAIK.  And of course you could do "NOT(h IS NULL)".

Certain Microsoft products that have difficulty grasping the concept of
NULL allow you to write these conditions with "=" or "!=", but in fact
any ordinary comparison operator should yield NULL out if either input
is NULL, so Microsoft is violating the letter and spirit of the spec by
accepting that.

			regards, tom lane