Skip to main content
Bitwise operators work on integers and are available on both backends. Bitmap functions operate on compressed roaring bitmaps (sets of UInt32 values) and are an advanced, ClickHouse-only feature: a bitmap is created with bitmapBuild and exported back to a normal array with bitmapToArray, so the aggregate/transform functions below are typically wrapped in bitmapToArray(...) to return a usable result.

​
bitAnd, bitOr, bitXor

Bitwise AND, OR, and XOR of two integers, applied bit by bit. Syntax
Arguments
  • a — first integer operand.
  • b — second integer operand.
Returned value
  • The bitwise combination of a and b, as an integer.
Example
Combine flag bits

​
bitNot, bitShiftLeft, bitShiftRight

Bitwise NOT (one’s complement) of an integer, and logical shifts of an integer’s bits left or right by n positions. Syntax
Arguments
  • a — the integer operand.
  • n — number of bit positions to shift by (shift functions only).
Returned value
  • The complemented or shifted integer.
Example
Invert and shift bits

​
bitCount, bitTest

bitCount returns how many bits are set to 1 in the binary representation of a number (its population count). bitTest returns the value (0 or 1) of the bit at a given position, counting from the least significant bit at position 0. Syntax
Arguments
  • x — the integer whose set bits are counted.
  • a — the integer to inspect.
  • i — zero-based bit position to test, from the least significant bit.
Returned value
  • bitCount returns the number of set bits, as an integer.
  • bitTest returns 1 if the bit at position i is set, otherwise 0.
Example
Count and test bits
Both are type-width dependent in ClickHouse: negative inputs, small integer types, and over-width bit indexes can diverge from PostgreSQL’s fixed 64-bit idiom.

​
bitRotateLeft, bitRotateRight

Rotate the bits of an integer left or right by n positions, wrapping bits shifted off one end back around to the other. Unlike shifts, no bits are lost (ClickHouse only). Syntax
Arguments
  • a — the unsigned integer to rotate (UInt8 / UInt16 / UInt32 / UInt64).
  • n — number of bit positions to rotate by.
Returned value
  • The rotated integer, of the same width as a.
Example
Rotate an 8-bit value
The result depends on the operand’s bit width. These functions are ClickHouse only because PostgreSQL has no unsigned integer types.

​
bitmapBuild, bitmapToArray

bitmapBuild constructs a roaring bitmap from an array of unsigned integers. bitmapToArray converts a bitmap back into a sorted Array(UInt32), and is used to export the result of any bitmap operation (ClickHouse only). Syntax
Arguments
  • array — an array of UInt* values to load into the bitmap (bitmapBuild).
  • bitmap — a roaring bitmap to export (bitmapToArray).
Returned value
  • bitmapBuild returns a bitmap object.
  • bitmapToArray returns an Array(UInt32) of the bitmap’s elements in ascending order.
Example
Round-trip an array through a bitmap

​
bitmapCardinality

Returns the number of elements stored in a bitmap (ClickHouse only). Syntax
Arguments
  • bitmap — the roaring bitmap to measure.
Returned value
  • The element count, as a UInt64.
Example
Count elements in a bitmap

​
bitmapMin, bitmapMax

Return the smallest and largest element in a bitmap (ClickHouse only). Syntax
Arguments
  • bitmap — the roaring bitmap to inspect.
Returned value
  • The smallest (bitmapMin) or largest (bitmapMax) element, as a UInt64. For an empty bitmap, bitmapMin returns the maximum unsigned value and bitmapMax returns 0.
Example
Smallest and largest elements

​
bitmapContains

Tests whether a bitmap contains a specific value (ClickHouse only). Syntax
Arguments
  • bitmap — the roaring bitmap to search.
  • value — the UInt32 element to look for.
Returned value
  • 1 if bitmap contains value, otherwise 0.
Example
Check membership

​
bitmapHasAll, bitmapHasAny

Compare two bitmaps for containment. bitmapHasAll checks whether the first bitmap contains every element of the second; bitmapHasAny checks whether they share at least one element (ClickHouse only). Syntax
Arguments
  • bitmap1 — the first roaring bitmap.
  • bitmap2 — the second roaring bitmap.
Returned value
  • bitmapHasAll returns 1 if every element of bitmap2 is present in bitmap1, otherwise 0.
  • bitmapHasAny returns 1 if the two bitmaps have any element in common, otherwise 0.
Example
Containment and overlap checks

​
bitmapAnd, bitmapOr, bitmapXor, bitmapAndnot

Set operations on two bitmaps: intersection (bitmapAnd), union (bitmapOr), symmetric difference (bitmapXor), and difference (bitmapAndnot, elements in the first bitmap but not the second). Each returns a bitmap, so wrap it in bitmapToArray to export the result (ClickHouse only). Syntax
Arguments
  • bitmap1 — the first roaring bitmap.
  • bitmap2 — the second roaring bitmap.
Returned value
  • A new bitmap holding the combined elements.
Example
Intersection, union, and difference of two bitmaps

​
bitmapAndCardinality, bitmapOrCardinality, bitmapXorCardinality, bitmapAndnotCardinality

Return the element count of a set operation on two bitmaps without materializing the resulting bitmap: the size of the intersection, union, symmetric difference, and difference respectively (ClickHouse only). Syntax
Arguments
  • bitmap1 — the first roaring bitmap.
  • bitmap2 — the second roaring bitmap.
Returned value
  • The number of elements in the corresponding set operation’s result, as a UInt64.
Example
Sizes of set operations

​
bitmapSubsetInRange, bitmapSubsetLimit, subBitmap

Extract a subset of a bitmap. bitmapSubsetInRange keeps elements whose value falls in the half-open range [range_start, range_end). bitmapSubsetLimit keeps up to cardinality_limit elements whose value is at least range_start. subBitmap skips offset elements (by position) and then keeps up to cardinality_limit of the remaining elements. Each returns a bitmap, so wrap it in bitmapToArray to export (ClickHouse only). Syntax
Arguments
  • bitmap — the source roaring bitmap.
  • range_start — inclusive lower bound on element value.
  • range_end — exclusive upper bound on element value (bitmapSubsetInRange).
  • cardinality_limit — maximum number of elements to keep.
  • offset — number of leading elements to skip, by position, zero-based (subBitmap).
Returned value
  • A new bitmap containing the selected subset.
Example
Range, value-limit, and positional subsets

​
bitmapTransform

Replaces specified elements of a bitmap with new values, according to matching from_array and to_array mappings. Returns a bitmap, so wrap it in bitmapToArray to export (ClickHouse only). Syntax
Arguments
  • bitmap — the source roaring bitmap.
  • from_array — array of existing element values to replace.
  • to_array — array of replacement values, positionally matched to from_array.
Returned value
  • A new bitmap with the mapped elements substituted.
Example
Remap two elements