Search Postgresql Archives

Re: Bitmask trickiness

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Jul 21, 2010 at 9:17 PM, Mathieu De Zutter <mathieu@xxxxxxxxxxxx> wrote:
> On Wed, Jul 21, 2010 at 5:59 AM, Howard Rogers <hjr@xxxxxxxxxx> wrote:
>> It's also easy to find records which have either some yellow or some
>> orange (or both) in them:
>>
>> select * from coloursample where colour & 10>0;
>>
>> But how do I find records which are ONLY yellow and orange, and
>> exclude records which have some other colour mixed in, in one simple
>> query without a lot of 'not this, not that' additions, and without
>> using multiple separate AND tests to nail it down?
>
> What about:
> WHERE colour & ~10 = 0
>
> Kind regards,
> Mathieu
>

Hi Mathieu:

Yes, that works for the simple case I gave by way of illustration (as
does the bitwise XOR -#- function), so thanks. But neither work very
obviously for the more realistic example I gave. If the stored value
is 21205 and I'm 'probing' it with 4098, the record should not be
returned, because 21205 implies a '1' bit is set, not the '2' bit. But
the # and ~ functions cause a non-negative result to be returned, so I
am none the wiser as to how to spot when or when not to return the
record:

In other words, this one is 'wrong' and should not be in the resultset:

ims=# select 21205 & ~ 4098;
 ?column?
----------
   17109
(1 row)

But this one is 'right' and should be returned:

ims=# select 21205 & ~ 4097;
 ?column?
----------
   17108
(1 row)

But looking at the outcome of both queries, there's nothing that
particularly leaps out at me that screams '17108' is right and '17109'
is wrong.

If I was 'probing' with a simple value (1,2,4,8 etc), then I can just
test for a non-zero return: if it's non-zero, the probe value is
implied by the stored value and the record should be returned. So,
right:

ims=# select 21205 & 1;
 ?column?
----------
       1
(1 row)

And wrong:

ims=# select 21205 & 2;
 ?column?
----------
       0
(1 row)

The minute you start probing with a complex value, however, (that is,
a probe value which is made up of multiple basic values, for example
4098, which is 4096 + 2) interpreting the output of the bitwise
operations becomes more than my head can cope with!

I fear I'm going to have to decompose the probe value supplied by a
user and perform multiple simple probes that match their meaning...
but with 15 possible attributes to deal with, that could get a lot
slower than I was hoping.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux