PostgreSQL 16 Adds Binary, Octal, and Hex Integer Literals

PostgreSQL 16 introduces a long-requested convenience for developers: the ability to write integer constants in binary, octal, and hexadecimal notation directly in SQL. Until now, PostgreSQL only accepted decimal integer literals, forcing developers to use base-10 calculations or workarounds when dealing with bitmasks, file permissions, memory addresses, or color codes.

The new syntax follows the draft SQL:202x standard and uses familiar prefixes:

  • 0x42F — hexadecimal
  • 0o273 — octal
  • 0b100101 — binary

The patch, authored by Peter Eisentraut and reviewed by John Naylor, Zhihong Yu, David Rowley, and Dean Rasheed, touches both the lexer and the integer type input functions. The roughly year-long development process included extensive testing and discussion within the PostgreSQL community about edge cases and implementation details.

Why Non-Decimal Notation Matters

Non-decimal integer literals reduce cognitive load and errors. Binary is how data is actually laid out in memory, so expressing values like file permission bitmasks or network masks directly in binary makes the intent explicit. Octal and hex are more compact than decimal for larger values, and they're already the lingua franca in many programming contexts.

A practical example: the maximum 32-bit unsigned integer is easy to express once you don't have to remember its decimal expansion:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

psql (16devel)

Type 'help' for help.

test=# select version();

                                                 version                                                  

----------------------------------------------------------------------------------------------------------

PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

(1 row)

test=# select 0xFFFFFFFF as maxint;

   maxint  

------------

4294967295

(1 row)

This matters in real schemas too. Suppose you want to store the Pantone colors of the Ukrainian flag — values that have meaning in hex:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

test=# create table country(

test(#   name text not null unique,

test(#   flag_colors integer[] not null

test(# );

CREATE TABLE

test=# insert into country values ('Ukraine', array[0x0057B7, 0xFFDD00]);

INSERT 0 1

test=# select * from country;

  name   |   flag_colors    

---------+------------------

Ukraine | {22455,16768256}

(1 row)

File metadata is another natural fit. When storing Unix permission bits, for instance, the octal representation directly mirrors the system-level notation:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

test=# create table file(

test(#   filename text not null unique,

test(#   permission integer not null

test(# );

CREATE TABLE

test=# insert into file values ('/usr/local/pgsql/bin/pg_ctl', 0o755); -- rwxr-xr-x

INSERT 0 1

test=# select * from file;

          filename           | permission

-----------------------------+-------------

/usr/local/pgsql/bin/pg_ctl |         493

(1 row)

Implementation Notes

Because the change touches the lexer, any query containing a number with one of these prefixes must be parsed correctly before the type input functions come into play. The full design rationale and all the corner cases surfaced during review are documented in the original discussion thread, which provides a useful window into how PostgreSQL evolves new features.

For more on what else ships in the upcoming release, keep an eye on the ongoing "what's new" series.