SET_BIT

Return a copy of the input value with the bit at the given position set to 0 or 1.

Category: miscReturns: BINARY | INTDialect: PostgreSql

Syntax

SET_BIT(value, position, new_value)

Description

## Overview Returns a copy of the input integer or binary value with the bit at the specified position set to the given new value (0 or 1). Use this function to flip feature flags, turn permission bits on or off, or edit a specific bit in a binary payload. SET_BIT is the inverse of GET_BIT: reading the same position after SET_BIT returns the value you wrote. For toggling rather than unconditional assignment, use BIT_XOR with `1 << position`. ## Behavior - Returns NULL if any argument is NULL. - Returns a value of the same type and width as the input `value` argument. - Position is zero-based; position 0 is the least significant bit. - Valid positions span 0 through the bit length of the value minus one. - `new_value` must be 0 or 1. Any other value raises an error. - Result is deterministic and side effect free. ## Bit semantics - `SET_BIT(x, n, 1)` is equivalent to `BIT_OR(x, 1 << n)`. - `SET_BIT(x, n, 0)` is equivalent to `BIT_AND(x, BIT_NOT(1 << n))`. - Writing the same value that is already at the position is a no-op and not an error. ## Compatibility - Matches the PG-compat SET_BIT function for both integer and BYTEA inputs.

Parameters

Name	Type	Description
`value`		Specifies the integer or binary value in which a bit is modified. The original value is not mutated; a new value is returned.
`position`		Specifies the zero-based bit position to update. Position 0 is the least significant bit. Must be non-negative and less than the bit length of the value.
`new_value`		Specifies the new bit value. Must be 0 or 1; any other value raises an error.

Examples

-- Set bit 0 of 0 to 1
SELECT SET_BIT(0, 0, 1);  -- 1

-- Set bit 3 of 0 to 1
SELECT SET_BIT(0, 3, 1);  -- 8

-- Clear bit 0 of 7 (binary 111)
SELECT SET_BIT(7, 0, 0);  -- 6

-- Grant a feature flag on a single row
UPDATE security.catalog.accounts
SET feature_flags = SET_BIT(feature_flags, 2, 1)
WHERE account_id = 42;

-- Revoke a permission bit in a projection
SELECT account_id, SET_BIT(permission_flags, 5, 0) AS without_admin
FROM security.catalog.accounts;

-- NULL propagation
SELECT SET_BIT(NULL, 0, 1);  -- NULL

Pitfalls

`new_value` must be exactly 0 or 1. Passing any other integer (including negative numbers or values greater than 1) raises an error; cast boolean predicates explicitly with `CASE WHEN cond THEN 1 ELSE 0 END`.
An out-of-range position raises an error rather than silently wrapping. Validate at the caller when the position comes from user input.
SET_BIT does not mutate in place; it returns a new value. Without an UPDATE statement the change is not persisted.
To toggle a bit rather than set it to a known value, use `BIT_XOR(x, 1 << n)`. SET_BIT cannot express toggling in a single call because `new_value` is an absolute assignment.