PostgreSQL to_hex() Function

The to_hex() function in PostgreSQL converts a specified number into the hexadecimal representation of that number. The hexadecimal is a number system having a base value of 16. There are a lot of scenarios where the hexadecimal numbers are used. So we need to see how we can convert a number into its hexadecimal representation in PostgreSQL.

In this article, we’ll learn to convert the numbers into hexadecimal numbers.

PostgreSQL to_hex() Function

The to_hex() function takes in the number and returns its hexadecimal representation. The syntax for the to_hex() function can be written as:

to_hex(num);

We need to provide an integer to the to_hex() function to get the string that will basically be the hexadecimal representation of that number.

The working of the to_hex() function can be presented by using the following examples.

Example 1: Understanding the to_hex() Function

We will execute the following query to better understand how the to_hex() function works.

SELECT
  to_hex(0) AS "hex:0",
  to_hex(9) AS "hex:9",
  to_hex(1178) AS "hex:1178",
  to_hex(-129) AS "hex:-129",
  to_hex(15) AS "hex:15";

The query will return the hexadecimal representation of all of the integers provided to the to_hex() function. The output looks like this:

We can clearly see that the output is in the hexadecimal representation of each integer. We can also note that the returned data type of the to_hex() function is TEXT.

We can also implement the to_hex() function on the table data.

Example 2: Using the to_hex() Function On Table’s Data

We can get the hexadecimal representation of all the entries of a column by using the to_hex() function. Let’s consider the following table named “num”.

Now to get the hexadecimal representation of all these numbers in the column named “x”, we will have to write the following query.

SELECT x, to_hex(x) AS "hexadecimal representation"
 FROM num;

The query returns the same output as expected i.e.

So this is how the to_hex() function works.

Conclusion

Postgres offers the to_hex() function to convert a number/numeric value into its hexadecimal representation. The parameter of the integer data type is given to the to_hex() function and in return it gives us the value in the string data type. This blog demonstrated the use of the to_hex() function with the help of examples.

PostgreSQL to_hex() Function