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.
Table of Contents
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.
Dean Rasheed and Peter Eisentraut implemented a patch which allows underscores in integer and numeric constants.
The commit message is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
Hint for bloggers. When you're generating zillions of test rows in your examples you can use exponential notation to immediately give your readers an idea about size of the row sets pic.twitter.com/6l2Vv1gqiV
— Pavlo Golub (@PavloGolub) January 24, 2023
Now we can use the underscores as well to nicely format our huge numbers:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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; |
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.
+43 (0) 2622 93022-0
office@cybertec.at