Waiting for PostgreSQL 16 – Non-decimal integer literals

On 14th of December 2022, Peter Eisentraut committed patch:

Non-decimal integer literals 
Add support for hexadecimal, octal, and binary integer literals:
per SQL:202x draft.
This adds support in the lexer as well as in the integer type input
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?
(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?
(1 row)
$ select 5678901234, 0x1527D27F2, 0o52237223762, 0b101010010011111010010011111110010 \gdesc
 ?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
        5678901234 as dec,
        0x1527D27F2 as hex,
        0o52237223762 as oct,
        0b101010010011111010010011111110010 as bin;

then the view will of course work, but it's definition will contain the number converted to decimal:

$ \d+ zz
                             View "public.zz"
 ColumnType  │ Collation │ NullableDefault │ Storage │ Description
 decbigint │           │          │         │ 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.