On 14th of December 2022, Peter Eisentraut committed patch:
Non-decimal integer literals
Add support for hexadecimal, octal, and binary integer literals:
0x42F
0o273
0b100101
per SQL:202x draft.
This adds support in the lexer as well as in the integer type input
functions.
Reviewed-by: John Naylor <john.naylor@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com
This is pretty cool. Basically the message from commit explains all, but let's see it in action:
$ select 1234, 0x4D2, 0o2322, 0b10011010010; ?column? │ ?column? │ ?column? │ ?column? ──────────┼──────────┼──────────┼────────── 1234 │ 1234 │ 1234 │ 1234 (1 row)
We can also use it to provide longer values, for int8, and it will automatically handle it:
$ select 5678901234, 0x1527D27F2, 0o52237223762, 0b101010010011111010010011111110010; ?column? │ ?column? │ ?column? │ ?column? ────────────┼────────────┼────────────┼──────────── 5678901234 │ 5678901234 │ 5678901234 │ 5678901234 (1 row) $ select 5678901234, 0x1527D27F2, 0o52237223762, 0b101010010011111010010011111110010 \gdesc Column │ Type ──────────┼──────── ?column? │ bigint ?column? │ bigint ?column? │ bigint ?column? │ bigint (4 rows)
The only thing you have to keep in mind is that PostgreSQL doesn't store the represenation, but rather a number. So, if you'd, for example, made a view:
$ create view zz as select 5678901234 as dec, 0x1527D27F2 as hex, 0o52237223762 as oct, 0b101010010011111010010011111110010 as bin; CREATE VIEW
then the view will of course work, but it's definition will contain the number converted to decimal:
$ \d+ zz View "public.zz" Column │ Type │ Collation │ Nullable │ Default │ Storage │ Description ────────┼────────┼───────────┼──────────┼─────────┼─────────┼───────────── dec │ bigint │ │ │ │ plain │ hex │ bigint │ │ │ │ plain │ oct │ bigint │ │ │ │ plain │ bin │ bigint │ │ │ │ plain │ View definition: SELECT '5678901234'::bigint AS "dec", '5678901234'::bigint AS hex, '5678901234'::bigint AS oct, '5678901234'::bigint AS bin;
That shouldn't be a problem, though, as the view will work just as well, and you most likely have it's definition in some migration/patch file anyway.
All in all, great stuff, and will simplify certain expressions. Thanks a lot to everyone that made it happen.