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
