Обсуждение: bitwise storage and operations

We've been storing some "enumerated"/"set" data in postgresql as INT or BIT(32) for several years for some
flags/toggleson records.  

This was preferable for storage to the ENUM type (or multiple columns), as we often changed the number of enumerated
optionsor their labels -- and computing everything in the application saved the trouble of database migrations.  This
hasworked out perfectly -- until today. 

For the first time ever, we need to run some queries that filter on these columns at the PostgreSQL level -- and I
can'tfigure out how. 

The documentation doesn't have any examples for SELECT for the bitwise operators, and everything I've found on various
threads/forumshas addressed inserts or converting on a select -- but never a comparison. 

I've tried numerous forms and have gotten as far as CASTing everything to BIT(n), but I can't seem to construct a valid
querythat can filter what I want. 

Can anyone share a sample WHERE clause or two that does a bitwise comparison against an INT or BIT column?

Thanks!

If it's in integer columns, bitwise logic works just like you would
expect it to as well.
https://www.postgresql.org/docs/current/static/functions-math.html

db=# select 'foo' where (9 & 1) > 0;
 ?column?
----------
 foo
(1 row)

db=# select 'foo' where (9 & 2) > 0;
 ?column?
----------
(0 rows)

Just bit-wise AND them and compare if the result is > 0.  If you use
the bitshift operator (<<) make sure you use parens to force ordering.
This is important.

On Mon, Sep 26, 2016 at 7:34 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Please include the list in all replies.
>
> On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
>>
>>
>> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote:
>>
>> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco <postgres@2xlp.com>
>> wrote:
>>>
>>> The documentation doesn't have any examples for SELECT for the bitwise
>>> operators,
>>
>>
>> That shows a simple computation.  One can "SELECT" any computation and get
>> a value.
>>
>> It doesn't show a bitwise operator being used against an INT or BIT
>> column, as I further elaborated.
>
>
> I assumed a certain level of familiarity with databases and provided enough
> info to answer your main question: "what are the available bit string
> operators?".  That you can apply these operator to either constants or
> columns was knowledge I took for granted.
>
>>
>> From what I can tell so far, i need to extract and compare a substring for
>> the (reverse) index of the particular bit I want to filter on.
>
>
> B'1001' is typed bit(4)...
>
> The only requirement with a WHERE clause is that the computation must result
> in a boolean.  My example SELECT computation does just that.  It uses
> "varbit" for convenience but INT can be CAST() to BIT and the operators
> themselves should operate on any of the BIT variants.
>
> What you want is the "bit-wise AND" operator and the equality operator, both
> of which you were shown.
>
> I'd suggest you put forth your own example, filling in pseudo-code where
> needed, if you wish for more specific advice.
>
> David J.
>

On Sep 27, 2016, at 10:54 AM, Brian Dunavant wrote:

> db=# select 'foo' where (9 & 1) > 0;

A HA

Thank you Brian and David -- I didn't realize that you needed to do the comparison to the result.

(or convert the result as these work):

    select 'foo' where (9 & 1)::bool;
    select 'foo' where bool(9 & 1);

I kept trying to figure out how to run operators on "9"  and "1" independently to create a boolean result.  I either
neededmore coffee or less yesterday. 

As a followup question...

Some searches suggested that Postgres can't use indexes of INTs for these comparisons, but could on bitwise string
columns.

One of these tables has over 30MM rows, so I'm trying to avoid a seq scan as much as possible.

I thought of creating a function index that casts my column to a bitstring, and then tailors searches onto that.
Forexample: 

    CREATE TEMPORARY TABLE example_toggle(
        id int primary key,
        toggle int default null
    );
    INSERT INTO example_toggle (id, toggle) VALUES (1, 1), (2, 2), (3, 3), (4, 5), (5, 8);
    CREATE INDEX idx_example_toggle_toggle_bit ON example_toggle(cast(toggle as bit(4)));

While these selects work...

    select * from example_toggle where (toggle & 1)::bool AND (toggle & 4)::bool;
    select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <>
0::bit(4));

Only about 200k items have a flag right now (out of 30MM) so I thought of using a partial index on the set flags.

The only way I've been able to get an index on the not null/0 used is to do the following:

    CREATE INDEX idx_example_toggle_toggle_bit ON example_toggle(cast(toggle as bit(4))) WHERE toggle <> 0;

then tweak the query with

    select * from example_toggle where (toggle & 1)::bool AND (toggle & 4)::bool AND (toggle > 0);
    select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <>
0::bit(4))AND (toggle > 0); 

obviously, the sample above is far too small for an index to be considered... but in general... is a partial index of
"toggle<> 0" and then hinting with "toggle > 0" the best way to only index the values that are not null or 0?