to_hex in PostgreSQL: Convert an Integer to a Hexadecimal String

Hexadecimal is a convenient human-readable format for everything that is really a number under the hood: colors, bit masks, permission flags, hashes. In PostgreSQL the to_hex(int) function takes an integer and returns its hex representation as a string, with no 0x prefix. Let's walk through practical scenarios and how to reverse the conversion.

Basic to_hex

The function accepts an integer or bigint and returns text:

SELECT to_hex(255);        -- 'ff'
SELECT to_hex(4096);       -- '1000'
SELECT to_hex(16777215);   -- 'ffffff'

Useful details:

• The result is always lowercase and has no leading zeros.
• There is no 0x prefix -- if you want one for display, add it yourself: '0x' || to_hex(255).
• For negative numbers PostgreSQL returns the two's complement hex, not a minus sign: to_hex(-1) yields 'ffffffff' for a 32-bit input.

When you need a fixed width (for example, two characters per byte), pad on the left with lpad:

-- Always two hex digits, e.g. for a single byte channel
SELECT lpad(to_hex(15), 2, '0');   -- '0f'

Colors and bit masks

A classic case is assembling a hex color from three RGB channels. Say the users table stores an avatar color as an integer:

SELECT id,
       '#' || lpad(to_hex(theme_color), 6, '0') AS css_color
FROM users
WHERE country = 'DE';

That turns the number 16711680 into #ff0000 -- pure red. Padding to six characters guarantees valid CSS even when the high bytes are zero.

A second frequent scenario is permission flags packed into one number with bitwise OR. Hex makes such masks readable:

-- 1=read, 2=write, 4=admin packed into orders.status as a bitmask
SELECT id,
       status,
       to_hex(status)        AS status_hex,
       (status & 4) <> 0     AS is_admin
FROM orders
WHERE (status & 4) <> 0;

Debugging flag values

When it is unclear which bits are set, render the number in three forms at once -- decimal, hex and binary:

SELECT salary,
       to_hex(salary::int)              AS hex,
       (salary::int)::bit(32)           AS bits
FROM employees
WHERE dept = 'engineering';

Gotcha: to_hex only works with integer types. Pass it a numeric or text and you get an error -- cast the input explicitly: to_hex(salary::int). And mind the sign: for bigint the two's complement is wider, so to_hex(-1::bigint) returns 'ffffffffffffffff', not 'ffffffff'.

Reversing hex back into a number

PostgreSQL has no direct from_hex(int), but there is a neat trick via the bit type: an x prefix in front of a hex literal defines a bit string that you can cast to an integer.

-- Parse hex text back into an integer
SELECT ('x' || lpad('ff', 8, '0'))::bit(32)::int;    -- 255
SELECT ('x' || lpad('1000', 8, '0'))::bit(32)::int;  -- 4096

The lpad to eight characters matters: bit(32) needs exactly 32 bits, that is 8 hex digits. For a bigint use bit(64) and lpad(..., 16, '0'). The full round trip:

-- Round trip: int -> hex -> int
SELECT ('x' || lpad(to_hex(48879), 8, '0'))::bit(32)::int;  -- 48879

bytea and MySQL equivalents

For binary data (bytea) rather than a single number, use encode and decode with the hex format:

-- Bytes to hex text and back
SELECT encode('PG'::bytea, 'hex');      -- '5047'
SELECT decode('5047', 'hex');           -- bytea \x5047

encode(..., 'hex') is ideal for hashes and raw bytes; to_hex is for numbers.

MySQL paints a different picture:

• HEX(255) returns 'FF' (uppercase!) and works on both numbers and strings.
• To reverse, CONV('ff', 16, 10) converts hex to decimal, or UNHEX('5047') for bytes.
• CONV(255, 10, 16) is a flexible swap between any two bases.

-- MySQL flavor
SELECT HEX(255),          -- 'FF'
       CONV('ff', 16, 10),-- '255'
       LPAD(HEX(15), 2, '0');  -- '0F'

ClickHouse offers hex(255) (uppercase too) and unhex('FF') for the reverse. Watch the case when comparing strings across engines. Bottom line: to_hex for numbers, encode/decode for bytes, and reverse via bit in PostgreSQL or CONV/UNHEX in MySQL.