0

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.

8
  • How many set and how many unset? May 13, 2022 at 4:04
  • @Charlieface it starts with everything unset. Then I gradually set the indexes until everything is set
    – Ashwin
    May 13, 2022 at 4:06
  • Well a row takes 23 bytes minimum plus an int size for this column. So if you just store the ones that are set, then you would have a table sized under 300MB, which isn't that big. A columnstore index would compress this down to a few KB. May 13, 2022 at 4:10
  • Why not only store the numbers that are set?
    – user1822
    May 13, 2022 at 5:21
  • @a_horse_with_no_name How do you suggest I store only numbers that are set?
    – Ashwin
    May 13, 2022 at 5:42

1 Answer 1

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

4
  • Thanks. This is what I was looking for. Btw, is this way of using bits less performant than the solution using rows (indexed)?
    – Ashwin
    May 13, 2022 at 16:36
  • 1
    @Ashwin: not sure. The solution with a bit string needs to rewrite the entire string (64kb) each time you want to update one value but is way more space efficient. If you store it in rows, an update of a single value only changes a (small) single row.
    – user1822
    May 13, 2022 at 17:25
  • Why does it have to re-write the entire string? It just has to set the bit at a particular position right?
    – Ashwin
    May 14, 2022 at 12:51
  • 1
    Because it's a single column. If you change a column, the whole column is written back to disk (in reality it's even the whole row). If you change a text column from 'bar' to 'bas' it also need to write the entire string, not just the changed character
    – user1822
    May 14, 2022 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.