GET_BIT

Return the value (0 or 1) of the bit at the given position in an integer or binary value.

Category: miscReturns: INTDialect: PostgreSql

Syntax

GET_BIT(value, position)

Description

## Overview Extracts a single bit from an integer or binary value and returns it as 0 or 1. Position 0 refers to the least significant bit. Use this function to test a specific flag in a bitmask, to expose individual bits as indicator columns, or to inspect a binary payload bit by bit. GET_BIT is the inverse of SET_BIT: they use the same addressing scheme and can round-trip a bitmap. ## Behavior - Returns NULL if either argument is NULL. - Returns an INT that is always 0 or 1. - Position is zero-based; position 0 is the least significant bit. - For integer inputs, valid positions span 0 through the integer width minus one (31 for INT, 63 for BIGINT). - For binary inputs, valid positions span 0 through `octet_length(value) * 8 - 1`. - Result is deterministic and side effect free. ## Bit semantics - Position 0 is least significant for both integer and binary operands. - `GET_BIT(x, n)` equals `BIT_AND(x >> n, 1)` on integers and is the canonical idiom for testing a single bit. - Out-of-range positions raise an error rather than silently returning 0. ## Compatibility - Matches the PG-compat GET_BIT function for both integer and BYTEA inputs.

Parameters

Name	Type	Description
`value`		Specifies the integer or binary value from which to read a bit. For integers the full promoted width is addressable; for binary values the length in bits is the length in bytes times 8.
`position`		Specifies the zero-based bit position to extract. Position 0 is the least significant bit. Must be non-negative and less than the bit length of the value.

Examples

-- Read bit 0 of 5 (binary 101)
SELECT GET_BIT(5, 0);  -- 1

-- Read bit 1 of 5 (binary 101)
SELECT GET_BIT(5, 1);  -- 0

-- Read bit 2 of 5 (binary 101)
SELECT GET_BIT(5, 2);  -- 1

-- Test whether a specific permission bit is set
SELECT user_id
FROM security.catalog.users
WHERE GET_BIT(permission_flags, 3) = 1;

-- Build an indicator column for a feature flag
SELECT account_id, GET_BIT(feature_flags, 7) AS has_beta_access
FROM security.catalog.accounts;

-- NULL propagation
SELECT GET_BIT(NULL, 0);  -- NULL

Pitfalls

Position is zero-based from the least significant bit. A user-facing 'bit 1' in documentation often means position 0 in this function; verify the convention before writing a predicate.
An out-of-range position raises an error. Guard with `position < BIT_LENGTH(value)` or clamp at the caller when the position is user-supplied.
Comparing `GET_BIT(x, n) = 1` is correct, but `GET_BIT(x, n)` alone is an INT, not a BOOLEAN. Use explicit comparison to avoid type errors when the result is used as a filter predicate.
For binary values, bit numbering within a byte is most-significant-bit first in some ecosystems; DeltaForge consistently treats position 0 as least significant across the entire value. Do not mix conventions.