varbitx extension for bit operations-PolarDB(PolarDB)-阿里云帮助中心

The built-in varbit extension in PostgreSQL Community Edition supports only basic BIT type operations. PolarDB for PostgreSQL extends the varbit extension with additional BIT operations for broader use cases, such as real-time user profiling recommendation systems, access control advertising systems, and ticketing systems.

Prerequisites

The version of PolarDB for PostgreSQL is PostgreSQL 11.

Functions

Function	Description
`get_bit (varbit a, int b, int c) returns varbit`	Extracts c bits starting from position b and returns a varbit value. Example: `get_bit('111110000011', 3, 5)` returns 11000.
`set_bit_array (varbit a, int b, int c, int[] d) returns varbit`	Sets the bits at the index positions specified in array d to b (0 or 1). Bits beyond the original length are filled with c (0 or 1). Example: `set_bit_array('111100001111', 0, 1, array[1,15])` returns 1011000011111110.
`bit_count (varbit a, int b, int c, int d) returns int`	Starting from position c, counts the number of b (0 or 1) values within d bits. If the range exceeds the bit string length, only the existing bits are counted. Example: `bit_count('1111000011110000', 1, 5, 4)` returns 1 (0001).
`bit_count (varbit a, int b) returns int`	Counts the number of b (1 or 0) values. Example: `bit_count('1111000011110000', 1)` returns 8.
`bit_fill (int a, int b) returns varbit`	Fills a bit string of length b with the value a (0 or 1). Example: `bit_fill(0,10)` returns 0000000000.
`bit_rand (int a, int b, float c) returns varbit`	Generates a random bit string of length a, with a random ratio of b (0 or 1) specified by c. Example: `bit_rand(10, 1, 0.3)` may return 0101000001.
`bit_posite (varbit a, int b, boolean c) returns int[]`	Returns the index position array of b (0 or 1). Indexing starts from 0. If c is true, positions are returned in ascending order. If c is false, positions are returned in descending order. Example: `bit_posite ('11110010011', 1, true)` returns [0,1,2,3,6,9,10], `bit_posite ('11110010011', 1, false)` returns [10,9,6,3,2,1,0].
`bit_posite (varbit a, int b, int c, boolean d) returns int[]`	Returns the index position array of b (0 or 1), up to c positions. Indexing starts from 0. If d is true, positions are returned in ascending order. If d is false, positions are returned in descending order. Example: `bit_posite ('11110010011', 1, 3, true)` returns [0,1,2], `bit_posite ('11110010011', 1, 3, false)` returns [10,9,6].
`get_bit_array (varbit a, int b, int c, int d) returns int[]`	Extracts c bits starting from position b and returns the index position array of d (0 or 1) values. Example: `get_bit_array('111110000011', 3, 5, 1)` returns the index array[3,4] for 11000.
`get_bit_array (varbit a, int b, int[] c) returns int[]`	Checks the bits at the index positions specified in array c for value b (0 or 1) and returns the matching index positions. Positions that exceed the bit string length are not included. Example: `get_bit_array('111110000011', 1, array[1,5,6,7,10,11])` returns array[1,10,11].
`set_bit_array (varbit a, int b, int c, int[] d, int e) returns varbit`	Sets the bits at the index positions specified in array d to b (0 or 1). Stops after setting e bits. Bits beyond the original length are filled with c (0 or 1). Example: `set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2)` returns 111111001111 (sets to 1, fills with 0 beyond the original length, and stops after setting 2 bits).
`set_bit_array_record (varbit a, int b, int c, int[] d) returns (varbit,int[])`	Sets the bits at the index positions specified in array d to b (0 or 1). Bits beyond the original length are filled with c (0 or 1). Returns the updated varbit value and the index position array of bits that were set to b in this operation. Example: `set_bit_array_record('111100001111', 0, 1, array[1,15])` returns 1011000011111110 (sets to 0, fills with 1 beyond the original length) and returns array[1,15].
`set_bit_array_record (varbit a, int b, int c, int[] d, int e) returns (varbit,int[])`	Sets the bits at the index positions specified in array d to b (0 or 1). Bits beyond the original length are filled with c (0 or 1). Stops after successfully setting e bits. Returns the updated varbit value and the index position array of bits that were set to b in this operation. Example: `set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2)` returns 111111001111 (sets to 1, fills with 0 beyond the original length, and stops after successfully setting 2 bits) and returns array[4,5] (array[1] was already 1, so it was not changed).
`bit_count_array (varbit a, int b, int[] c) returns int`	Counts the number of 0 or 1 values at the specified index positions. Example: `bit_count_array('1111000011110000', 1, array[1,2,7,8])` returns 3.

Usage

Create the extension
```
CREATE EXTENSION varbitx;
```
Delete the extension
```
DROP EXTENSION varbitx;
```

Use functions

Use select <function>. Examples:

bit_count function

select bit_count('1111000011110000', 1, 5, 4);

The following output is returned:

 bit_count   
-----------  
         1  
(1 row)

set_bit_array_record function

select set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2);

The following output is returned:

  set_bit_array_record    
------------------------  
 (111111001111,"{4,5}")  
(1 row)

For more information about the functions, see Functions.