PG
PRO
⬛

PostgreSQL Bit String Functions

Complete reference for PostgreSQL bit string functions covering get_bit(), set_bit(), and bitwise AND/OR/XOR/NOT operations on the bit(n) and varbit types. Includes length, overlay, positional operations, and concatenation on bit strings. Updated for PostgreSQL 16.

6 functions

Jump to function

& (bitwise AND)# (bitwise XOR)| (bitwise OR)bit_lengthget_bitset_bit

What are PostgreSQL Bit String Functions?

PostgreSQL bit string functions operate on the bit(n) and varbit types, which store fixed or variable-length sequences of bits. They support bitwise operations (AND, OR, XOR, NOT), individual bit access via get_bit() and set_bit(), concatenation, and substring operations. Bit strings are useful for storing compact flag sets, permission bitmasks, and binary feature vectors.

& (bitwise AND)

PG 7.4+→ bit varying

Performs bitwise AND on two bit strings of the same length.

DeveloperData Eng

Signature

bit varying & bit varying → bit varying

Parameters

ParameterTypeDescription
bits1bit or bit varyingFirst bit string
bits2bit or bit varyingSecond bit string (same length)

Examples

sql
SELECT B'10001101' & B'01101100';
→00001100
sql
SELECT permissions & B'00000111' FROM roles;
→Keep only bits 5, 6, 7 (last three permissions)
sql
SELECT (permissions & B'00000011') = B'00000011' AS has_read_and_write FROM roles;
→true/false — both bits 6 and 7 must be set
sql
SELECT role_name FROM roles WHERE (permissions & B'11000000') <> B'00000000';
→Roles with at least one of the top two permissions set
⚠Anti-Pattern— Applying a bitstring mask of wrong length to a BIT VARYING column

Both operands of `&` must have identical bit lengths or PostgreSQL raises an error. A common mistake is applying an 8-bit mask to a `BIT VARYING` column that stores fewer bits, breaking the query at runtime.

✓ Instead: Cast to a fixed `BIT(n)` before applying masks, or ensure the mask literal matches the exact declared width of the column: `(perms::bit(8)) & B'00000111'`.

Apply a bitmask with `&` to check multiple permissions at once: `permissions & mask = mask` is true only if all bits in the mask are set.

example
SELECT role_name FROM roles WHERE (permissions & B'00000011') = B'00000011';
→Roles with both bit 0 and bit 1 permissions
See also:bitwise_orbitwise_xorget_bitset_bit_bitstring

# (bitwise XOR)

PG 7.4+→ bit varying

Performs bitwise XOR on two bit strings. Bits are set where the operands differ.

DeveloperData Eng

Signature

bit varying # bit varying → bit varying

Parameters

ParameterTypeDescription
bits1bit or bit varyingFirst bit string
bits2bit or bit varyingSecond bit string (same length)

Examples

sql
SELECT B'10001101' # B'01101100';
→11100001
sql
SELECT permissions # B'00000001' FROM roles;
→Toggles bit 0 in all roles

XOR with itself always yields all-zero — useful for identity checks

sql
SELECT B'11001010' # B'11001010';
→00000000
sql
UPDATE roles SET permissions = permissions # B'00000011' WHERE role_name = 'toggle_test';
→Atomically toggles bits 6 and 7 (two permissions) in one update
⚠Anti-Pattern— Using XOR to toggle permissions (accidentally granting instead of revoking)

XOR toggle flips bits regardless of their current state. In permission systems, using XOR on an unknown current value can silently grant a permission you intended to revoke, or revoke one you intended to grant.

✓ Instead: Use `perms | mask` to deliberately grant (set bits to 1) and `perms & ~mask` (or `& (B'11111111' # mask)`) to deliberately revoke (clear bits to 0). Reserve XOR for explicit toggle use cases where both outcomes are acceptable.

`bits # mask` toggles every bit in the mask: 0 becomes 1 and 1 becomes 0. Useful for flip-toggle operations without knowing the current state.

example
UPDATE roles SET permissions = permissions # B'00000001' WHERE role_name = 'toggle_test';
→Toggles the read permission bit
See also:bitwise_andbitwise_orget_bit

| (bitwise OR)

PG 7.4+→ bit varying

Performs bitwise OR on two bit strings, setting bits that are set in either operand.

DeveloperData Eng

Signature

bit varying | bit varying → bit varying

Parameters

ParameterTypeDescription
bits1bit or bit varyingFirst bit string
bits2bit or bit varyingSecond bit string (same length)

Examples

sql
SELECT B'10001101' | B'01101100';
→11101101
sql
SELECT permissions | B'00000001' FROM roles;
→Add bit 0 (read permission) to all roles

Merge multiple feature flags in one expression

sql
SELECT B'00000000' | B'00001100' | B'00110000';
→00111100
sql
UPDATE roles SET permissions = permissions | B'00000111' WHERE role_name = 'editor';
→Grants bits 5, 6, 7 (three permissions) to editor role in one operation
⚠Anti-Pattern— Trying to use bitstring OR as an aggregate to combine flags across rows

There is no built-in `bit_or` aggregate for `BIT VARYING` — the `|` operator is a binary operator, not an aggregate. Attempting to combine flags from multiple rows with `|` in a GROUP BY query produces a syntax or type error.

✓ Instead: Use integer columns and PostgreSQL's built-in `bit_or()` aggregate for accumulating flags across rows. For bit strings, write a custom aggregate or cast to integer first.

Add a permission bit with `permissions | mask` — this sets additional bits without clearing any. Equivalent to `set_bit` for multiple bits at once.

example
UPDATE roles SET permissions = permissions | B'00000001' WHERE role_name = 'viewer';
→Adds read permission (bit 0) to viewer role
See also:bitwise_andbitwise_xorget_bitset_bit_bitstring

bit_length

PG 7.4+→ integer

Returns the number of bits in a bit string.

DeveloperData Eng

Signature

bit_length ( bit varying ) → integer

Parameters

ParameterTypeDescription
bitsbit or bit varyingBit string to measure

Examples

sql
SELECT bit_length(B'10101010');
→8
sql
SELECT bit_length(B'101');
→3

Explicit cast to varbit — still returns exact bit count

sql
SELECT bit_length(B'10101010'::bit varying);
→8
sql
SELECT bit_length(flags) FROM feature_flags WHERE id = 1;
→Number of bits in the stored flag mask
⚠Anti-Pattern— Calling bit_length on fixed-width BIT(n) columns to check their width

For a column declared `BIT(8)`, `bit_length` always returns 8 — the result is a compile-time constant, not useful runtime information. Using it in a WHERE clause or ORDER BY on fixed-width columns wastes computation.

✓ Instead: Use `bit_length` only on `BIT VARYING` columns where lengths can differ at runtime. For fixed-width BIT types, rely on the declared type constraint.

Unlike integers, bit string types store exactly the number of bits you declare. `bit(8)` always has 8 bits; `bit varying(32)` can have 1–32. Use `bit_length` to verify the actual width of a `varbit` value.

example
SELECT bit_length(permission_bits) FROM roles;
→Actual bit count of permission bitmask
See also:octet_lengthlength

get_bit

PG 7.4+ for bit varying; get_bit on text/bytea arguments: PG 14+→ integer

Extracts the nth bit from a bit string (0-based indexing from the left). Returns 0 or 1.

DeveloperData Eng

Signature

get_bit ( bits bit varying, n integer ) → integer

Parameters

ParameterTypeDescription
bitsbit varying or byteaBit string or binary string
ninteger0-based bit index

Examples

Leftmost bit

sql
SELECT get_bit(B'10110101', 0);
→1
sql
SELECT get_bit(B'10110101', 1);
→0
sql
SELECT get_bit(permission_bits, 3) AS has_permission_3 FROM roles;
→1 or 0 for permission bit 3

Rightmost bit (index = bit_length - 1)

sql
SELECT get_bit(B'10110101', 7);
→1
⚠Anti-Pattern— Off-by-one: using get_bit index 1 when you mean the first (leftmost) bit

`get_bit(bits, 0)` returns the leftmost (most-significant) bit. Developers familiar with 1-based arrays often use index 1, silently reading the second bit and getting wrong permission checks that pass security reviews.

✓ Instead: Use `get_bit(perms, 0)` for the first bit. Document which bit index corresponds to which permission at the schema level to prevent off-by-one errors across the codebase.

Store permission flags as `bit(N)` where each bit represents one permission. Use `get_bit(perms, n) = 1` to check individual permissions without bitwise integer arithmetic.

example
SELECT role_name, get_bit(permissions, 0) AS can_read, get_bit(permissions, 1) AS can_write, get_bit(permissions, 2) AS can_delete FROM roles;
→Individual permission bits as boolean columns
See also:set_bitbit_length_bitstring

set_bit

PG 7.4+ for bit varying; set_bit on bytea: PG 14+→ bit varying

Sets the nth bit in a bit string to newvalue (0 or 1).

DeveloperData Eng

Signature

set_bit ( bits bit varying, n integer, newvalue integer ) → bit varying

Parameters

ParameterTypeDescription
bitsbit varying or byteaBit string to modify
ninteger0-based bit index
newvalueintegerNew value: 0 or 1

Examples

sql
SELECT set_bit(B'10110101', 1, 1);
→11110101
sql
SELECT set_bit(permissions, 2, 1) AS with_delete_perm FROM roles WHERE role_name = 'editor';
→Permission bits with bit 2 (delete) set to 1

Clears the leftmost (most-significant) bit

sql
SELECT set_bit(B'11111111', 0, 0);
→01111111
sql
UPDATE roles SET permissions = set_bit(permissions, 3, 0) WHERE role_name = 'viewer';
→Revokes permission bit 3 from viewer role
⚠Anti-Pattern— Chaining set_bit calls on the same variable instead of nesting them

`set_bit` returns a new value — it does not modify in place. Assigning the result back to a variable and then calling `set_bit` on it again in separate statements (in PL/pgSQL) without using the return value discards the first change.

✓ Instead: Nest calls: `set_bit(set_bit(perms, 0, 1), 2, 1)`, or use bitwise OR with a mask `perms | B'00000101'` to set multiple bits atomically.

Use `set_bit(permissions, n, 1)` to grant permission n without touching other bits. Use `set_bit(permissions, n, 0)` to revoke it. This is cleaner than bitwise OR/AND with integer masks.

example
UPDATE roles SET permissions = set_bit(permissions, 2, 1) WHERE role_name = 'editor';
→Grants delete permission to editor role
See also:get_bitbit_length_bitstring

Related PostgreSQL Categories

Binary Strings →Logical Operators →Comparison Operators →