Search Shortcut cmd + k | ctrl + k
Search cmd+k ctrl+k
Dark Mode
1.1 (stable)
Bitstring Functions

This section describes functions and operators for examining and manipulating `BITSTRING` values. Bitstrings must be of equal length when performing the bitwise operands AND, OR and XOR. When bit shifting, the original length of the string is preserved.

## Bitstring Operators

The table below shows the available mathematical operators for `BIT` type.

Operator Description Example Result
`&` Bitwise AND `'10101'::BITSTRING & '10001'::BITSTRING` `10001`
`|` Bitwise OR `'1011'::BITSTRING | '0001'::BITSTRING` `1011`
`xor` Bitwise XOR `xor('101'::BITSTRING, '001'::BITSTRING)` `100`
`~` Bitwise NOT `~('101'::BITSTRING)` `010`
`<<` Bitwise shift left `'1001011'::BITSTRING << 3` `1011000`
`>>` Bitwise shift right `'1001011'::BITSTRING >> 3` `0001001`

## Bitstring Functions

The table below shows the available scalar functions for `BIT` type.

Name Description
`bit_count(bitstring)` Returns the number of set bits in the bitstring.
`bit_length(bitstring)` Returns the number of bits in the bitstring.
`bit_position(substring, bitstring)` Returns first starting index of the specified substring within bits, or zero if it's not present. The first (leftmost) bit is indexed 1.
`bitstring(bitstring, length)` Returns a bitstring of determined length.
`get_bit(bitstring, index)` Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0.
`length(bitstring)` Alias for `bit_length`.
`octet_length(bitstring)` Returns the number of bytes in the bitstring.
`set_bit(bitstring, index, new_value)` Sets the nth bit in bitstring to newvalue; the first (leftmost) bit is indexed 0. Returns a new bitstring.

#### `bit_count(bitstring)`

 Description Returns the number of set bits in the bitstring. Example `bit_count('1101011'::BITSTRING)` Result `5`

#### `bit_length(bitstring)`

 Description Returns the number of bits in the bitstring. Example `bit_length('1101011'::BITSTRING)` Result `7`

#### `bit_position(substring, bitstring)`

 Description Returns first starting index of the specified substring within bits, or zero if it's not present. The first (leftmost) bit is indexed 1 Example `bit_position('010'::BITSTRING, '1110101'::BITSTRING)` Result `4`

#### `bitstring(bitstring, length)`

 Description Returns a bitstring of determined length. Example `bitstring('1010'::BITSTRING, 7)` Result `0001010`

#### `get_bit(bitstring, index)`

 Description Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0. Example `get_bit('0110010'::BITSTRING, 2)` Result `1`

#### `length(bitstring)`

 Description Alias for `bit_length`. Example `length('1101011'::BITSTRING)` Result `7`

#### `octet_length(bitstring)`

 Description Returns the number of bytes in the bitstring. Example `octet_length('1101011'::BITSTRING)` Result `1`

#### `set_bit(bitstring, index, new_value)`

 Description Sets the nth bit in bitstring to newvalue; the first (leftmost) bit is indexed 0. Returns a new bitstring. Example `set_bit('0110010'::BITSTRING, 2, 0)` Result `0100010`

## Bitstring Aggregate Functions

These aggregate functions are available for `BIT` type.

Name Description
`bit_and(arg)` Returns the bitwise AND operation performed on all bitstrings in a given expression.
`bit_or(arg)` Returns the bitwise OR operation performed on all bitstrings in a given expression.
`bit_xor(arg)` Returns the bitwise XOR operation performed on all bitstrings in a given expression.
`bitstring_agg(arg)` Returns a bitstring with bits set for each distinct position defined in `arg`.
`bitstring_agg(arg, min, max)` Returns a bitstring with bits set for each distinct position defined in `arg`. All positions must be within the range [`min`, `max`] or an `Out of Range Error` will be thrown.

#### `bit_and(arg)`

 Description Returns the bitwise AND operation performed on all bitstrings in a given expression. Example `bit_and(A)`

#### `bit_or(arg)`

 Description Returns the bitwise OR operation performed on all bitstrings in a given expression. Example `bit_or(A)`

#### `bit_xor(arg)`

 Description Returns the bitwise XOR operation performed on all bitstrings in a given expression. Example `bit_xor(A)`

#### `bitstring_agg(arg)`

 Description The `bitstring_agg` function takes any integer type as input and returns a bitstring with bits set for each distinct value. The left-most bit represents the smallest value in the column and the right-most bit the maximum value. If possible, the min and max are retrieved from the column statistics. Otherwise, it is also possible to provide the min and max values. Example `bitstring_agg(A)`

Tip The combination of `bit_count` and `bitstring_agg` can be used as an alternative to `count(DISTINCT ...)`, with possible performance improvements in cases of low cardinality and dense values.

#### `bitstring_agg(arg, min, max)`

 Description Returns a bitstring with bits set for each distinct position defined in `arg`. All positions must be within the range [`min`, `max`] or an `Out of Range Error` will be thrown. Example `bitstring_agg(A, 1, 42)`