⬛

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

bit_length get_bit set_bit & (bitwise AND)| (bitwise OR)# (bitwise XOR)

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.

bit_length

PG 7.4+→ integer

Returns the number of bits in a bit string.

DeveloperData Eng

Signature

bit_length ( bit varying ) → integer

Parameters

Parameter	Type	Description
bits	bit or bit varying	Bit 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

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

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

Parameter	Type	Description
bits	bit varying or bytea	Bit string or binary string
n	integer	0-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

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

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

Parameter	Type	Description
bits	bit varying or bytea	Bit string to modify
n	integer	0-based bit index
newvalue	integer	New 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

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

& (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

Parameter	Type	Description
bits1	bit or bit varying	First bit string
bits2	bit or bit varying	Second 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

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

| (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

Parameter	Type	Description
bits1	bit or bit varying	First bit string
bits2	bit or bit varying	Second 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

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

# (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

Parameter	Type	Description
bits1	bit or bit varying	First bit string
bits2	bit or bit varying	Second 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

`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

PostgreSQL Bit String Functions

Jump to function

What are PostgreSQL Bit String Functions?

bit_length

Signature

Parameters

Examples

get_bit

Signature

Parameters

Examples

set_bit

Signature

Parameters

Examples

& (bitwise AND)

Signature

Parameters

Examples

| (bitwise OR)

Signature

Parameters

Examples

# (bitwise XOR)

Signature

Parameters

Examples

Related PostgreSQL Categories