hex, oct, bin integers in PostgreSQL 16

12.2022 / Category: / Tags: | |

SQL and integer notations

NEW in PostgreSQL 16 - support for non-decimal notation of integer constants! PostgreSQL already has powerful support for string constants, with E't', E'11', E'u0009' and U&'009' all meaning the same thing (a “horizontal tab” character). However, PostgreSQL's support for numerical constants has - up until now - been rather limited; it didn't allow you to write integer numbers in bases other than 10. But PostgreSQL 16 implements the SQL:202x standard (draft), allowing you to use binary, octal and hexadecimal notation for integers.

The advantage of using non-decimal notation are:
- It makes it easier to read and understand the code.
- Binary notation is used to store data in a computer's memory, so using binary notation in code makes it easier to interact with data stored in memory.
- Octal and hexadecimal notations allow for more concise representation of larger numbers.
- Octal and hexadecimal notations are more familiar to programmers and computer scientists.

What new integer notation will PostgreSQL 16 have?

Peter Eisentraut implemented a patch which adds support for the hexadecimal, octal, and binary integer literals:

  • 0x42F
  • 0o273
  • 0b100101

The patch was reviewed and committed by John Naylor, Zhihong Yu, David Rowley, and Dean Rasheed.

It took almost a year to solve all the issues and to provide all the tests. Changes were introduced not only to the lexer but also to the integer type input functions. You can read more about input functions in the manual.

As usual, I strongly encourage you to read the whole discussion which happened during the implementation of this functionality. You can get an idea of how the community operates and what pitfalls may arise out of the blue! Consider this email thread a detective story! 🕵️


Do you remember the maximum 32-bit unsigned integer value? Me neither. You don't need to now!

The two hues that are mesmerizing the world right now are the colors of the Ukrainian flag.

Do you know Pantone has labeled them “Freedom Blue” and “Energizing Yellow”, a resounding representation of the importance of freedom from oppression and the energetic determination of a country to defend itself from a foreign invader?

So next time you want to have a country table, don't forget to store the flag colors, e.g.

Or maybe you want to store information about some file?


PostgreSQL 16 will support hexadecimal, octal, and binary integer literals as 0x, 0o, and 0b prefixes. These notations make it easier to read and understand code, interact with data stored in memory, and represent larger numbers more concisely. Check out out the "what's new" post series to know more about upcoming PostgreSQL versions.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram