BIT_NOT

Return the bitwise complement of an integer value.

Category: miscReturns: INTDialect: PostgreSql

Syntax

BIT_NOT(n)

Description

## Overview Returns the bitwise complement of the input integer, where every bit is inverted from 0 to 1 and from 1 to 0. Under two's complement representation, this is algebraically equal to `-(n + 1)`. Use this function to construct clear-masks (paired with BIT_AND) or to flip every bit of a bitmap. BIT_NOT is the function form of the unary `~` operator. ## Behavior - Returns NULL if the argument is NULL. - Operates on all bits of the promoted integer width. For an INT the width is 32 bits, for BIGINT it is 64 bits. - Applied twice, returns the original value: BIT_NOT(BIT_NOT(x)) = x. - Result is deterministic and side effect free. - BIT_NOT(0) is -1 (all ones). BIT_NOT(-1) is 0. ## Bit semantics - Uses two's complement arithmetic, so negative results are expected for non-negative inputs with the sign bit flipped. - To produce an unsigned view, mask after complementing: `BIT_AND(BIT_NOT(x), 0xFFFFFFFF)` keeps only the low 32 bits. - Common idiom: `BIT_AND(flags, BIT_NOT(mask))` clears the bits set in `mask`. ## Compatibility - Equivalent to the standard `~` operator and to the PG-compat function of the same name.

Parameters

Name	Type	Description
`n`		Specifies the integer value whose bits are to be inverted. Every bit is flipped across the full width of the argument type.

Examples

-- BIT_NOT(0) flips every bit to 1 (all ones is -1 in two's complement)
SELECT BIT_NOT(0);  -- -1

-- Double complement returns the original value
SELECT BIT_NOT(BIT_NOT(42));  -- 42

-- Build a mask that clears bit 2 and AND it with flags
SELECT BIT_AND(flags, BIT_NOT(4)) AS cleared_bit_2
FROM security.catalog.settings;

-- Algebraic identity: BIT_NOT(n) equals -(n + 1)
SELECT BIT_NOT(10) AS complement, -(10 + 1) AS negated_plus_one;

-- NULL propagation
SELECT BIT_NOT(NULL);  -- NULL

-- Cast narrower integers before complementing to control width
SELECT BIT_NOT(CAST(7 AS INT)) AS width_32;  -- -8

Pitfalls

The result depends on integer width. `BIT_NOT(CAST(7 AS SMALLINT))` yields -8 as a 16-bit value, but promotion to INT or BIGINT can produce different bit patterns when mixing types. Cast explicitly when the width matters.
BIT_NOT returns a signed value. If you need an unsigned bit pattern (for example, to write into a HEX column), mask the result with an all-ones value of the target width.
Do not use BIT_NOT on non-integer types. The function does not accept BYTEA or BINARY values; use bit string operators for those types.
BIT_NOT(0) is -1, not 0xFFFFFFFF as a literal; two's complement representation means the numeric value is -1 regardless of the bit pattern.