How to use the built-in varbit plugin for PostgreSQL - ApsaraDB RDS

The varbitx plug-in extends the varbit plug-in in ApsaraDB RDS for PostgreSQL with additional bit string operations. The standard varbit plug-in from the PostgreSQL community edition supports only basic BIT-type functions. varbitx adds functions for slicing, bulk-setting, position lookup, and counting—covering use cases such as real-time user profile recommendation, access control advertising, and ticketing.

Prerequisites

Before you begin, ensure that your RDS instance runs one of the following PostgreSQL versions:

PostgreSQL 11
PostgreSQL 10

Install and remove the plug-in

Install the varbitx plug-in:

CREATE EXTENSION varbitx;

Remove the plug-in:

DROP EXTENSION varbitx;

Supported functions

Call all functions with a SELECT statement:

SELECT <function>;

The following sections list each function with its signature, description, and a worked example in input → output format.

Extraction functions

Functions that read bits from a bit string without modifying it.

Function	Description	Example
`get_bit(varbit a, int b, int c) returns varbit`	Returns `c` bits starting at position `b` as a varbit string.	`get_bit('111110000011', 3, 5)` → `11000`
`get_bit_array(varbit a, int b, int c, int d) returns int[]`	Extracts `c` bits starting at position `b`, then returns the 0-indexed positions of bits whose value equals `d` (0 or 1).	`get_bit_array('111110000011', 3, 5, 1)` → `[3,4]`
`get_bit_array(varbit a, int b, int[] c) returns int[]`	Reads the bits at positions specified by subscript array `c`, then returns the positions of bits whose value equals `b` (0 or 1). Positions not in `c` are ignored.	`get_bit_array('111110000011', 1, array[1,5,6,7,10,11])` → `[1,10,11]`

Mutation functions

Functions that return a modified copy of the bit string.

Function	Description	Example
`set_bit_array(varbit a, int b, int c, int[] d) returns varbit`	Sets the bits at positions `d` to value `b` (0 or 1). Bits beyond the original length are filled with value `c` (0 or 1).	`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 the 4-argument form, but returns exactly `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 `d` to value `b` (0 or 1). Bits beyond the original length are filled with value `c`. Returns both the modified 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 the 4-argument form, 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]`

Generation functions

Functions that produce a new bit string.

Function	Description	Example
`bit_fill(int a, int b) returns varbit`	Returns a bit string of length `b` filled entirely with value `a` (0 or 1).	`bit_fill(0, 10)` → `0000000000`
`bit_rand(int a, int b, float c) returns varbit`	Returns a bit string of length `a` where approximately `c` (0.0–1.0) of the bits are randomly set to value `b` (0 or 1).	`bit_rand(10, 1, 0.3)` may return `0101000001`

Count functions

Functions that count bits matching a given value.

Function	Description	Example
`bit_count(varbit a, int b, int c, int d) returns int`	Counts bits with value `b` (0 or 1), starting at position `c`, across `d` bits. Bits beyond the specified range are not counted.	`bit_count('1111000011110000', 1, 5, 4)` → `1`
`bit_count(varbit a, int b) returns int`	Counts all bits with value `b` (0 or 1) in the entire bit string.	`bit_count('1111000011110000', 1)` → `8`
`bit_count_array(varbit a, int b, int[] c) returns int`	Counts bits with value `b` (0 or 1) at the positions specified by subscript array `c`.	`bit_count_array('1111000011110000', 1, array[1,2,7,8])` → `3`

Position lookup functions

Functions that return the positions of bits matching a given value.

Function	Description	Example
`bit_posite(varbit a, int b, boolean c) returns int[]`	Returns the 0-indexed positions of all bits with value `b` (0 or 1). Pass `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 up to `c` positions of bits with value `b` (0 or 1). Pass `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]`

Examples

Count set bits in a range

Count bits with value 1, starting at position 5, across 4 bits:

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

Output:

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

Set bits and return changed positions

Set bits at positions 1, 4, 5, 6, and 7 to value 1, stop after changing 2 bits, and return the modified string with the changed positions:

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

Output:

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