BIT_AND

Return the bitwise AND of two integer values.

Category: miscReturns: INTDialect: PostgreSql

Syntax

BIT_AND(a, b)

Description

## Overview Returns an integer whose bits are the bitwise AND of the corresponding bits in the two operands. A bit in the result is 1 only if the bit at the same position is 1 in both inputs. Use this function to mask values, isolate flags in a bitmap, or test for the presence of specific feature bits. BIT_AND is the function form of the `&` operator and is often clearer in generated SQL, in pipelines that avoid operator overloading, or when composing with other BIT_* helpers like BIT_NOT and BIT_OR. ## Behavior - Returns NULL if either argument is NULL. - Operates on the two's complement bit pattern of the integers. Negative numbers have their sign bit set. - Both arguments are promoted to the widest integer type among the two before the operation. - Result is deterministic and side effect free. - No overflow is possible: the result always fits in the input width. - BIT_AND(x, x) equals x. BIT_AND(x, 0) equals 0. BIT_AND(x, -1) equals x (all-ones mask). ## Bit semantics - Bits are numbered from 0 at the least significant end. - The operation works on all bits of the promoted integer width. For an INT argument the width is 32 bits; for BIGINT it is 64 bits. - To clear a specific bit n, AND with the complement of `1 << n`: `BIT_AND(x, BIT_NOT(1 << n))`. ## Compatibility - Equivalent to the standard `&` operator and to the PG-compat function of the same name.

Parameters

Name	Type	Description
`a`		Specifies the first integer operand. Treated as a two's complement signed value across the full width of the argument type.
`b`		Specifies the second integer operand. Must be the same integer width as the first operand after implicit promotion.

Examples

-- Basic bitwise AND of 12 (1100) and 10 (1010)
SELECT BIT_AND(12, 10);  -- 8

-- Clear every bit by ANDing with zero
SELECT BIT_AND(255, 0);  -- 0

-- Mask to the lower 4 bits
SELECT BIT_AND(user_flags, 15) AS low_nibble
FROM security.catalog.user_flags;

-- Test whether a feature flag bit is set
SELECT account_id
FROM security.catalog.accounts
WHERE BIT_AND(feature_flags, 4) <> 0;

-- NULL propagation
SELECT BIT_AND(NULL, 7);  -- NULL

-- Combine with BIT_NOT to clear a specific bit
SELECT BIT_AND(flags, BIT_NOT(4)) AS cleared_bit_2
FROM security.catalog.user_flags;

Pitfalls

A bit test written as `BIT_AND(flags, mask) = mask` is correct only when `mask` is a single bit; for multi-bit masks, use `BIT_AND(flags, mask) = mask` when all bits must be set, or `BIT_AND(flags, mask) <> 0` when any bit is sufficient.
The function does not coerce strings. Passing a numeric-looking string raises a type error; cast explicitly with TRY_CAST_INT when reading from text columns.
Integer promotion can surprise you: ANDing an INT with a BIGINT produces a BIGINT. Cast the result back to INT if you are writing into a narrower column.
BIT_AND with a negative operand treats the sign bit as a normal bit. `BIT_AND(-1, x)` always equals x (-1 is all ones in two's complement).