# Waiting for PostgreSQL 17 – Add to_bin() and to_oct().

On 23rd of August 2023, Nathan Bossart committed patch:

This commit introduces functions for converting numbers to their
equivalent binary and octal representations.  Also, the base
conversion code for these functions and to_hex() has been moved to
a common helper function.

Reviewed-by: Ian Barwick, Dag Lem, Vignesh C, Tom Lane, Peter Eisentraut, Kirk Wolak, Vik Fearing, John Naylor, Dean Rasheed

Since 2002 and version 7.3 we had to_hex:

\$ SELECT to_hex(1234567);
to_hex
────────
12d687
(1 ROW)

Now, we got complementary functions that work with binary and octal numbers:

\$ SELECT to_bin(1234567), to_oct(1234567);
to_bin         │ to_oct
───────────────────────┼─────────
1001011010110100001114553207
(1 ROW)

They don't work (all three) with fractions:

\$ SELECT to_hex(1.1);
ERROR:  FUNCTION to_hex(NUMERIC) does NOT exist
LINE 1: SELECT to_hex(1.1);
^
HINT:  No FUNCTION matches the given name AND argument types. You might need TO ADD explicit TYPE casts.

While adding these, docs also got clarification – all these functions, including the old to_hex, work in two's complement representation. Which means that the values for negative numbers might be surprising to some:

\$ select i, to_hex(i), to_bin(i), to_oct(i) from unnest(‘{1,0,-1}'::int4[]) i;
i │ to_hex │ to_bin │ to_oct
────┼──────────┼──────────────────────────────────┼─────────────
1 │ 1 │ 1 │ 1
0 │ 0 │ 0 │ 0
-1 │ ffffffff │ 11111111111111111111111111111111 │ 37777777777
(3 rows)

Now, you could ask – how to convert in the other direction? That's simple – just prefix the value with:

• 0x – for hexadecimal values
• 0o – for octal values
• 0b – for binary values

like this:

\$ SELECT '0xdecaf'::int4;
int4
────────
912559
(1 ROW)

\$ SELECT '0o666'::int4;
int4
──────
438
(1 ROW)

\$ SELECT '0b10101010'::int4;
int4
──────
170
(1 ROW)

Cool addition, it will make life easier for developers, thanks a lot.

This site uses Akismet to reduce spam. Learn how your comment data is processed.