Postgres Pro
Downloads
Home   >   mailing lists

Thread: why these results?

why these results?

From
Wes James
Date:
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

Re: why these results?

From
Adrian Klaver
Date:
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

Re: why these results?

From
Reinoud van Leeuwen
Date:
On Mon, Aug 01, 2011 at 04:50:00PM -0600, 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.  ??

Wild guess:
select count (*) from table where col is null;
count
------ 1
(1 row)

";-)

Reinoud
-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v@n.leeuwen.net
http://reinoud.van.leeuwen.net kvk 27320762
__________________________________________________

Re: why these results?

From
Steve Crawford
Date:
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

Re: why these results?

From
Wes James
Date:
Yes.  Thanks to all that responded.  That was it.

-wes

On Mon, Aug 1, 2011 at 5:01 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>