Varbitx extension functions and usage - PolarDB:varbitx

The varbitx extension in PolarDB for PostgreSQL (Compatible with Oracle) expands on the PostgreSQL Community edition varbit type, adding bit operations for use cases that the built-in functions don't cover—such as real-time user profile recommendations, access control advertising, and ticketing systems.

Prerequisites

Before using varbitx, install the extension in your database:

CREATE EXTENSION varbitx;

To remove the extension:

DROP EXTENSION varbitx;

Functions

All functions operate on varbit (variable-length bit string) values. Subscripts start at 0.

Function	Description	Example
`get_bit(varbit a, int b, int c) returns varbit`	Returns `c` bits starting at position `b`.	`get_bit('111110000011', 3, 5)` → `11000`
`set_bit_array(varbit a, int b, int c, int[] d) returns varbit`	Sets the bits at positions in subscript array `d` to value `b` (0 or 1). Fills bits beyond the original length with value `c`.	`set_bit_array('111100001111', 0, 1, array[1,15])` → `1011000011111110`
`set_bit_array(varbit a, int b, int c, int[] d, int e) returns varbit`	Same as above, but limits the returned bit string to `e` bits.	`set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2)` → `111111001111`
`set_bit_array_record(varbit a, int b, int c, int[] d) returns (varbit, int[])`	Sets the bits at positions in subscript array `d` to value `b`. Fills beyond-length bits with `c`. Returns both the updated bit string and a subscript array of the changed positions.	`set_bit_array_record('111100001111', 0, 1, array[1,15])` → `(1011000011111110, {1,15})`
`set_bit_array_record(varbit a, int b, int c, int[] d, int e) returns (varbit, int[])`	Same as above, but stops after changing `e` bits and returns immediately.	`set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2)` → `(111111001111, {4,5})`
`bit_count(varbit a, int b, int c, int d) returns int`	Counts occurrences of value `b` (0 or 1) among `d` bits starting at position `c`. Bits beyond the string length are not counted.	`bit_count('1111000011110000', 1, 5, 4)` → `1`
`bit_count(varbit a, int b) returns int`	Counts all occurrences of value `b` in the bit string.	`bit_count('1111000011110000', 1)` → `8`
`bit_count_array(varbit a, int b, int[] c) returns int`	Counts occurrences of value `b` among the bits at positions specified by subscript array `c`.	`bit_count_array('1111000011110000', 1, array[1,2,7,8])` → `3`
`bit_fill(int a, int b) returns varbit`	Returns a bit string of `b` bits all set to value `a` (0 or 1).	`bit_fill(0, 10)` → `0000000000`
`bit_rand(int a, int b, float c) returns varbit`	Generates a bit string of `a` bits, randomly setting approximately `c` percent of them to value `b`.	`bit_rand(10, 1, 0.3)` may return `0101000001`
`bit_posite(varbit a, int b, boolean c) returns int[]`	Returns the positions of all bits with value `b`. Set `c` to `true` for ascending order, `false` for descending.	`bit_posite('11110010011', 1, true)` → `[0,1,2,3,6,9,10]`; `bit_posite('11110010011', 1, false)` → `[10,9,6,3,2,1,0]`
`bit_posite(varbit a, int b, int c, boolean d) returns int[]`	Returns the first `c` positions of bits with value `b`. Set `d` to `true` for ascending order, `false` for descending.	`bit_posite('11110010011', 1, 3, true)` → `[0,1,2]`; `bit_posite('11110010011', 1, 3, false)` → `[10,9,6]`
`get_bit_array(varbit a, int b, int c, int d) returns int[]`	Extracts `c` bits starting at position `b`, then returns positions where the value equals `d`.	`get_bit_array('111110000011', 3, 5, 1)` → `[3,4]` (from extracted string `11000`)
`get_bit_array(varbit a, int b, int[] c) returns int[]`	Extracts bits at positions specified by subscript array `c`, then returns positions where the value equals `b`. Positions not in `c` are not counted.	`get_bit_array('111110000011', 1, array[1,5,6,7,10,11])` → `[1,10,11]`

Usage

Call any varbitx function using a SELECT statement.

bit_count

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

Result:

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

set_bit_array_record

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

Result:

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

For the full function reference, see Functions supported by varbitx.