In PostgreSQL, we can use the to_hex() function to convert a number to its equivalent hexadecimal representation.

The function accepts the number as an integer or bigint, and it returns the result as a text representation of its hexadecimal equivalent.

Example

Here’s an example to demonstrate:

SELECT to_hex(14);

Result:

e

The result is e because that’s the hexadecimal equivalent of 14 in decimal.

Here are some more examples:

SELECT 
    to_hex(57) AS "57",
    to_hex(570) AS "570",
    to_hex(571) AS "571",
    to_hex(572) AS "572",
    to_hex(-57) AS "-57",
    to_hex(-572) AS "-572",
    to_hex(34294657934) AS "34294657934";

Result:

 57 | 570 | 571 | 572 |   -57    |   -572   | 34294657934 
----+-----+-----+-----+----------+----------+-------------
 39 | 23a | 23b | 23c | ffffffc7 | fffffdc4 | 7fc1ef38e

Maximum Range

The to_hex() function accepts a bigint value, so we can do this:

SELECT 
    to_hex(9223372036854775807) AS "9223372036854775807",
    to_hex(-9223372036854775808) AS "-9223372036854775808";

Result:

 9223372036854775807 | -9223372036854775808 
---------------------+----------------------
 7fffffffffffffff    | 8000000000000000

But we can’t go outside that range. Here’s what happens when we go outside the range:

SELECT to_hex(9223372036854775808) AS "9223372036854775808";

Result:

ERROR:  function to_hex(numeric) does not exist
LINE 1: SELECT to_hex(9223372036854775808) AS "9223372036854775808";
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Understanding Hexadecimal

Most of us are familiar with the decimal system when using numbers. This can also be referred to as base-10. In base-10, we use different digits for the first ten numbers (including zero), and then we kind of start again, combining digits from there on after. So after 9 comes 10, which is a combination of the first two digits (0 and 1).

Hexadecimal on the other hand, is referred to as base-16. That’s because we use a different digit for the first 16 numbers (including zero), and then we start again, combining the previous digits for each incremental number.

So when we use a system where numeric digits only run between 0 and 9, where do we get the extra six for hexadecimal? In hexadecimal, once we’ve reached 9, we can then use A, B, C, D, E, and F to bring us up to the hex equivalent of 15. We can start combining digits for any number that’s higher than that.

The following table demonstrates this concept, and shows how decimal numbers can be mapped to their hexadecimal equivalents:

Decimal	Hexadecimal
0	0
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	A
11	B
12	C
13	D
14	E
15	F
16	10
17	11
18	12

And so on…