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

Add to_bin() and to_oct().
 
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.
 
Co-authored-by: Eric Radman
Reviewed-by: Ian Barwick, Dag Lem, Vignesh C, Tom Lane, Peter Eisentraut, Kirk Wolak, Vik Fearing, John Naylor, Dean Rasheed
Discussion: https://postgr.es/m/Y6IyTQQ/TsD5wnsH%40vm3.eradman.com

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
───────────────────────┼─────────
 100101101011010000111 │ 4553207
(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.