Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 6A5DEB5DC12
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 20:01:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 42765-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 23:01:32 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from polaris.pinpointresearch.com (cerberus.pinpointresearch.com
	[66.7.238.130])
	by mail.postgresql.org (Postfix) with ESMTP id 4A95AB5DC04
	for <pgsql-sql@postgresql.org>; Mon,  1 Aug 2011 20:01:31 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 6CFC3E00EC83
	for <pgsql-sql@postgresql.org>; Mon,  1 Aug 2011 16:01:30 -0700 (PDT)
Message-ID: <4E37304A.2020801@pinpointresearch.com>
Date: Mon, 01 Aug 2011 16:01:30 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: why these results?
References: 
 <CAFjCMHuntvFO7SsGYNQrDjUwXazfnu5dgYA0BOh4eX0VcABzeA@mail.gmail.com>
In-Reply-To: 
 <CAFjCMHuntvFO7SsGYNQrDjUwXazfnu5dgYA0BOh4eX0VcABzeA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.623 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RP_MATCHES_RCVD=-0.723
X-Spam-Level: 
X-Archive-Number: 201108/4
X-Sequence-Number: 35672

On 08/01/2011 03:50 PM, Wes James wrote:
> select count(*) from table;
>
> count
> -------
>     100
> (1 row)
>
>
> is correct
>
> select count(*) from table where col::text ~~* '%text%';
>
> count
> -------
>       1
> (1 row)
>
> is correct.
>
> But now if I do:
>
>
> select count(*) from table where col::text !~~* '%text%';
> count
> -------
>    98
> (1 row)
>
> Shouldn't it be 99?  That is out of 100 records there is one that has
> "text" in column "col" so the !~~* should return 99 rows.  ??
>
> -wes
>
select count(*) from table where col is null;

(null is neither equal nor not-equal to anything, even null)

Cheers,
Steve