All we need is an easy explanation of the problem, so here it is.
I have a long list of indexes/numbers from
0 to 10 million. I would like to mark each of these numbers as
true/false or set/unset. I want to avoid creating a table with a row for everynumber like
CREATE TABLE foo (number integer, set boolean)
Instead, I was thinking that I could do some bitwise computation to store and retrive this data from a single cell. Can I use the
Bit(N) data type here?
CREATE TABLE some_table (my_store BIT(1000000)) // 1 million
How should I go about setting/unsetting/retriving a bit value at a particular position?
I found that
BIT(10000000) gives an error
is out of range for type integer.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value.
set_bit() won’t automatically increase the size of the bit string (if using
bit varying), and
get_bit() will throw an error when trying to access a bit beyond the length of the current value.
create table numbers (flags bit(10000000)); insert into numbers (flags) values (repeat('0', 10000000)::bit(10000000));
That creates a single bit string with 10 million bits (all set to zero).
Note that the numbering of the bits starts at
0, not 1. So to test for the "last value" you need:
select get_bit(flags, 10e6::int - 1) from numbers;
To change a value, use
set_bit() to set the bit for Number 10000
update numbers set flags = set_bit(flags, 10000 - 1, 1)
You can wrap the logic into functions if you want.
In theory you could use a
bit varying to support an unknown range of numbers, you just need to have some logic that extends the current value to include the new bit position e.g. by appending the appropriate number of bits between the new one and the existing ones to "fill the gap".
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂