All Products
Search

This Product

    PolarDB:varbitx

    Document Center

    PolarDB:varbitx

    Last Updated:Mar 28, 2026

    The varbitx extension for PolarDB for PostgreSQL (Compatible with Oracle) adds advanced bit string operations beyond what PostgreSQL's built-in bit functions provide. Use it for scenarios that require high-performance bitwise manipulation on large datasets, such as real-time user profile recommendation, access control for advertising systems, and ticketing.

    Prerequisites

    Before you begin, ensure that you have:

    • A PolarDB for PostgreSQL (Compatible with Oracle) cluster running PostgreSQL 11

    Install and remove the extension

    Install varbitx:

    CREATE EXTENSION varbitx;

    Remove varbitx:

    DROP EXTENSION varbitx;

    Function reference

    All functions operate on varbit bit strings. Subscripts are zero-based throughout.

    get_bit

    get_bit(source varbit, start int, count int) → varbit

    Returns count bits starting at position start.

    get_bit('111110000011', 3, 5) → 11000

    set_bit_array

    Overload 1 — set bits by subscript array, extend as needed:

    set_bit_array(source varbit, bit_value int, fill int, positions int[]) → varbit

    Sets the bits at each position in positions to bit_value (0 or 1). Bits beyond the original length are filled with fill.

    set_bit_array('111100001111', 0, 1, array[1,15]) → 1011000011111110

    Overload 2 — process only the first limit positions:

    set_bit_array(source varbit, bit_value int, fill int, positions int[], limit int) → varbit

    Same as overload 1, but stops after processing limit positions from the array.

    set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2) → 111111001111

    set_bit_array_record

    Like set_bit_array, but returns both the modified bit string and a subscript array of the positions that were actually changed.

    Overload 1 — process all positions:

    set_bit_array_record(source varbit, bit_value int, fill int, positions int[]) → (varbit, int[])

    set_bit_array_record('111100001111', 0, 1, array[1,15]) → 1011000011111110, {1,15}

    Overload 2 — stop after limit changes:

    set_bit_array_record(source varbit, bit_value int, fill int, positions int[], limit int) → (varbit, int[])

    set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2) → 111111001111, {4,5}

    bit_count

    Overload 1 — count matching bits in a range:

    bit_count(source varbit, target int, start int, count int) → int

    Counts occurrences of target (0 or 1) among count bits starting at start. Bits beyond the string length are not counted.

    bit_count('1111000011110000', 1, 5, 4) → 1

    Overload 2 — count all matching bits:

    bit_count(source varbit, target int) → int

    bit_count('1111000011110000', 1) → 8

    bit_count_array

    bit_count_array(source varbit, target int, positions int[]) → int

    Counts occurrences of target among the bits at the positions listed in positions. Bits not in positions are ignored.

    bit_count_array('1111000011110000', 1, array[1,2,7,8]) → 3

    bit_fill

    bit_fill(bit_value int, count int) → varbit

    Returns a bit string of count bits, all set to bit_value (0 or 1).

    bit_fill(0, 10) → 0000000000

    bit_rand

    bit_rand(count int, bit_value int, ratio float) → varbit

    Returns a bit string of count bits where approximately ratio of the bits are randomly set to bit_value (0 or 1).

    bit_rand(10, 1, 0.3) → may return 0101000001

    bit_posite

    Returns a subscript array of positions where bits match target. Pass ascending = true for ascending order, false for descending.

    Overload 1 — return all matching positions:

    bit_posite(source varbit, target int, ascending boolean) → int[]

    bit_posite('11110010011', 1, true) → [0,1,2,3,6,9,10]

    bit_posite('11110010011', 1, false) → [10,9,6,3,2,1,0]

    Overload 2 — return at most limit matching positions:

    bit_posite(source varbit, target int, limit int, ascending boolean) → int[]

    bit_posite('11110010011', 1, 3, true) → [0,1,2]

    bit_posite('11110010011', 1, 3, false) → [10,9,6]

    get_bit_array

    Returns subscripts of bits matching target within a selected subset.

    Overload 1 — select by range, then filter:

    get_bit_array(source varbit, start int, count int, target int) → int[]

    Gets count bits from start, then returns the subscripts of those that match target.

    get_bit_array('111110000011', 3, 5, 1) → [3,4] (the extracted segment is 11000)

    Overload 2 — select by subscript array, then filter:

    get_bit_array(source varbit, target int, positions int[]) → int[]

    Reads only the bits at positions, then returns the subscripts of those that match target.

    get_bit_array('111110000011', 1, array[1,5,6,7,10,11]) → [1,10,11]

    Usage examples

    Call any function with a SELECT statement.

    Count matching bits in a range of a bitmap

    The following example counts how many bits are set to 1 in positions 5 through 8 of a 16-bit bitmap:

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

    Result:

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

    Activate specific feature flags and track which positions changed

    In an access control system, you can use set_bit_array_record to enable a limited number of permissions at once and get back exactly which positions were updated. The following example enables the first two positions from the candidate array [1, 4, 5, 6, 7] and returns the updated bitmap along with the changed positions:

    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)

    Positions 4 and 5 were set to 1. The returned subscript array {4,5} confirms which flags changed, useful for audit logging or downstream processing.