Marking/retrieving a long array of numbers as "true/false" in postgres

Question

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?

EDIT

I found that BIT(10000000) gives an error is out of range for type integer.

Answer 1

Both, bit and 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.

Online example

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".