Underscores in numeric constants in PostgreSQL 16

SQL and numeric constants

NEW in PostgreSQL 16 – support for underscores in integer and numeric constants! I already wrote about support for different notations in the upcoming PostgreSQL 16. This new major version also implements the SQL:202x standard (draft), allowing you to use underscore separators for integers and numeric constants.

Using underscore separators in integer and numeric constants makes the numbers more readable and easier to understand. For example, instead of writing 1000000000, you can write 1_000_000_000, which makes it easier to see that the number represents a billion. This is especially useful in large or complex numbers, where it can be difficult to identify the value of each digit. The use of underscores in this way is a convention used in many programming languages.

What new numeric constants notation will PostgreSQL 16 have?

Dean Rasheed and Peter Eisentraut implemented a patch which allows underscores in integer and numeric constants.
The commit message is:

This allows underscores to be used in integer and numeric literals - 
and their corresponding type input functions - for visual grouping.
For example:

    1_500_000_000
    3.14159_26535_89793
    0xffff_ffff
    0b_1001_0001

A single underscore is allowed between any 2 digits, or immediately
after the base prefix indicator of non-decimal integers, per SQL:202x
draft.

Peter Eisentraut and Dean Rasheed

Discussion: https://postgr.es/m/84aae844-dc55-a4be-86d9-4f0fa405cc97%40enterprisedb.com

Earlier in my tweet I proposed to use exponential notation in blog posts to ease the perception.

By loading the tweet, you agree to Twitter’s privacy policy.
Learn more

Load tweet

Always unblock Twitter Tweets

Now we can use the underscores as well to nicely format our huge numbers:

timetable=# SELECT 1_000_000;
 ?column? 
----------
  1000000
(1 row)

timetable=# SELECT 1_2_3;
 ?column? 
----------
      123
(1 row)

timetable=# SELECT 0x1EEE_FFFF;
 ?column?  
-----------
 518979583
(1 row)

timetable=# SELECT 0o2_73;
 ?column? 
----------
      187
(1 row)

timetable=# SELECT 0b_10_0101;
 ?column? 
----------
       37
(1 row)

timetable=# SELECT 1_000.000_005;
  ?column?   
-------------
 1000.000005
(1 row)

timetable=# SELECT 1_000.;
 ?column? 
----------
     1000
(1 row)

timetable=# SELECT .000_005;
 ?column? 
----------
 0.000005
(1 row)

timetable=# SELECT 1_000.5e0_1;
 ?column? 
----------
    10005
(1 row)

But please remember that leading, trailing and double underscores will produce an error:

timetable=#  SELECT _100;
ERROR:  column "_100" does not exist
LINE 1: SELECT _100;
               ^
timetable=#  SELECT 100_;
ERROR:  trailing junk after numeric literal at or near "100_"
LINE 1: SELECT 100_;
               ^
timetable=#  SELECT 100__000;
ERROR:  trailing junk after numeric literal at or near "100_"
LINE 1: SELECT 100__000;

tl;dr

PostgreSQL 16 will support hexadecimal, octal, and binary integer literals as 0x, 0o, and 0b prefixes and underscore as a separator. 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.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.