Re: [GENERAL] bitwise storage and operations
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 needed more 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. For example: 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bitwise storage and operations
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 wrote: > Please include the list in all replies. > > On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco wrote: >> >> >> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote: >> >> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco >> 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. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bitwise storage and operations
Please include the list in all replies. On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco wrote: > > On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote: > > On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco > 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.
Re: [GENERAL] bitwise storage and operations
On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco wrote: > The documentation doesn't have any examples for SELECT for the bitwise > operators, Um... https://www.postgresql.org/docs/9.5/static/functions-bitstring.html SELECT B'111'::varbit & B'101'::varbit = B'101'::varbit SELECT 10::bit(8)::varbit David J.
[GENERAL] 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/toggles on records. This was preferable for storage to the ENUM type (or multiple columns), as we often changed the number of enumerated options or their labels -- and computing everything in the application saved the trouble of database migrations. This has worked 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't figure out how. The documentation doesn't have any examples for SELECT for the bitwise operators, and everything I've found on various threads/forums has 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 query that 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
