TO_HEX

Return the lowercase hexadecimal string representation of an integer.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

TO_HEX(n)

Description

## Overview Converts the given integer to its lowercase hexadecimal string representation. No '0x' prefix is added. TO_HEX is convenient for producing compact stable identifiers from integer hashes, rendering bitmap values, and generating diagnostic output in hex. For negative values, TO_HEX returns the 64-bit two's-complement representation, which is a 16-character lowercase hex string starting with 'f'. Zero returns the single character '0'; positive values are not zero-padded. ## Behavior - Returns NULL when the input is NULL. - Returns '0' for input 0. - Positive values are rendered in the minimum number of lowercase hex digits needed (no leading zeros). - Negative values are rendered as the 16-character 64-bit two's-complement form. - No '0x' prefix is emitted. - Accepts SMALLINT, INTEGER, and BIGINT inputs; smaller types are widened before conversion. ## Compatibility - Matches the common SQL TO_HEX semantics for lowercase output and no prefix. - For uppercase output, wrap in UPPER(TO_HEX(n)). For fixed-width output, wrap in LPAD.

Parameters

Name	Type	Description
`n`		Specifies the integer value to convert. Accepts both positive and negative values.

Examples

-- Convert 255
SELECT TO_HEX(255);  -- 'ff'

-- Convert 16
SELECT TO_HEX(16);  -- '10'

-- Zero
SELECT TO_HEX(0);  -- '0'

-- 16-bit maximum
SELECT TO_HEX(65535);  -- 'ffff'

-- Negative value uses 64-bit two's complement
SELECT TO_HEX(-1);  -- 'ffffffffffffffff'

-- NULL propagates
SELECT TO_HEX(CAST(NULL AS INT));  -- NULL

Pitfalls

Negative values use 64-bit two's complement, so TO_HEX(-1) is 16 characters, not a dash followed by '1'. Cast to an unsigned representation first if you need magnitude-based hex.
No '0x' prefix is emitted. If downstream tools expect a prefix, prepend it explicitly.
Output is lowercase. Wrap in UPPER for uppercase hex.
No zero padding is applied. LPAD(TO_HEX(n), 8, '0') gives an 8-digit fixed-width hex string for 32-bit values.