Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 541F8B5DC0D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 20:00:48 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 51271-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 23:00:40 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f44.google.com (mail-pz0-f44.google.com
	[209.85.210.44])
	by mail.postgresql.org (Postfix) with ESMTP id 6C744B5DC08
	for <pgsql-sql@postgresql.org>; Mon,  1 Aug 2011 20:00:40 -0300 (ADT)
Received: by pzk5 with SMTP id 5so35530912pzk.17
	for <pgsql-sql@postgresql.org>; Mon, 01 Aug 2011 16:00:40 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=from:to:subject:date:user-agent:cc:references:in-reply-to
	:mime-version:content-type:content-transfer-encoding:message-id;
	bh=qP/d+2WVb70jGL7w2aerQQBdZV9KYz0kCMn/VlVe3qs=;
	b=VRIA6Qj8lYRCbV7rthfI6Y0OSuvmEgjiOJTjJ8yzzG3LwRab1H/+YqgpFWCZYAcysw
	BOekDqF0skHZxonCjuKQrL1GyvK+YvWeRomsGDy8b6xwYfoWSgQT8o/vzwELUSxzh1L3
	+94FR13CdnjTXNH9ObNqEJMNoC/L61reoeKj0=
Received: by 10.68.52.134 with SMTP id t6mr8917924pbo.505.1312239636128;
	Mon, 01 Aug 2011 16:00:36 -0700 (PDT)
Received: from tucker.localnet (c-24-17-164-54.hsd1.wa.comcast.net
	[24.17.164.54])
	by mx.google.com with ESMTPS id i9sm6026917pbk.52.2011.08.01.16.00.33
	(version=SSLv3 cipher=OTHER); Mon, 01 Aug 2011 16:00:35 -0700 (PDT)
From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: why these results?
Date: Mon, 1 Aug 2011 16:00:31 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.32-33-generic; KDE/4.4.5; i686; ; )
Cc: Wes James <comptekki@gmail.com>
References: 
 <CAFjCMHuntvFO7SsGYNQrDjUwXazfnu5dgYA0BOh4eX0VcABzeA@mail.gmail.com>
In-Reply-To: 
 <CAFjCMHuntvFO7SsGYNQrDjUwXazfnu5dgYA0BOh4eX0VcABzeA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201108011600.31822.adrian.klaver@gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201108/2
X-Sequence-Number: 35670

On Monday, August 01, 2011 3:50:00 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.  ??

NULL  value in field?

> 
> -wes

-- 
Adrian Klaver
adrian.klaver@gmail.com