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.