Here is a patch to add support for underscores in numeric literals, for
visual grouping, like
1_500_000_000
0b10001000_00000000
0o_1_755
0xFFFF_FFFF
1.618_034
per SQL:202x draft.
This adds support in the lexer as well as in the integer type input
functions.
TODO: float/numeric type input support
I did some performance tests similar to what was done in [0] and didn't
find any problematic deviations. Other tests would be welcome.
[0]:
https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb...@enterprisedb.comFrom 36aef78423e9adc6ebe72fb2a3cf43e385a2caca Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 27 Dec 2022 10:10:18 +0100
Subject: [PATCH v1] Underscores in numeric literals
Add support for underscores in numeric literals, for visual grouping,
like
1_500_000_000
0b10001000_00000000
0o_1_755
0xFFFF_FFFF
1.618_034
per SQL:202x draft.
This adds support in the lexer as well as in the integer type input
functions.
TODO: float/numeric type input support
Discussion:
https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb...@enterprisedb.com
---
doc/src/sgml/syntax.sgml | 14 ++
src/backend/catalog/sql_features.txt | 1 +
src/backend/parser/scan.l | 63 ++++++--
src/backend/utils/adt/numutils.c | 144 ++++++++++++++++--
src/fe_utils/psqlscan.l | 16 +-
src/interfaces/ecpg/preproc/pgc.l | 16 +-
src/pl/plpgsql/src/expected/plpgsql_trap.out | 2 +-
src/pl/plpgsql/src/sql/plpgsql_trap.sql | 2 +-
src/test/regress/expected/int2.out | 44 ++++++
src/test/regress/expected/int4.out | 44 ++++++
src/test/regress/expected/int8.out | 44 ++++++
src/test/regress/expected/numerology.out | 92 ++++++++++-
src/test/regress/expected/partition_prune.out | 6 +-
src/test/regress/sql/int2.sql | 14 ++
src/test/regress/sql/int4.sql | 14 ++
src/test/regress/sql/int8.sql | 14 ++
src/test/regress/sql/numerology.sql | 24 ++-
src/test/regress/sql/partition_prune.sql | 6 +-
18 files changed, 509 insertions(+), 51 deletions(-)
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 956182e7c6..27e53b4b46 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -728,6 +728,20 @@ <title>Numeric Constants</title>
</para>
</note>
+ <para>
+ For visual grouping, underscores can be inserted between digits. These
+ have no further effect on the value of the literal. For example:
+<literallayout>1_500_000_000
+0b10001000_00000000
+0o_1_755
+0xFFFF_FFFF
+1.618_034
+</literallayout>
+ Underscores are not allowed at the start or end of a numeric constant or
+ a group of digits (that is, immediately before or after a period or the
+ <quote>e</quote>), and more than one underscore in a row is not allowed.
+ </para>
+
<para>
<indexterm><primary>integer</primary></indexterm>
<indexterm><primary>bigint</primary></indexterm>
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index abad216b7e..3766762ae3 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -528,6 +528,7 @@ T653 SQL-schema statements in external routines
YES
T654 SQL-dynamic statements in external routines NO
T655 Cyclically dependent routines YES
T661 Non-decimal integer literals YES SQL:202x draft
+T662 Underscores in integer literals YES SQL:202x draft
T811 Basic SQL/JSON constructor functions NO
T812 SQL/JSON: JSON_OBJECTAGG NO
T813 SQL/JSON: JSON_ARRAYAGG with ORDER BY NO
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index 9ad9e0c8ba..a1ea94ef06 100644
--- a/src/backend/parser/scan.l
+++ b/src/backend/parser/scan.l
@@ -124,6 +124,7 @@ static void addlit(char *ytext, int yleng, core_yyscan_t
yyscanner);
static void addlitchar(unsigned char ychar, core_yyscan_t yyscanner);
static char *litbufdup(core_yyscan_t yyscanner);
static unsigned char unescape_single_char(unsigned char c, core_yyscan_t
yyscanner);
+static char *strip_underscores(const char *in);
static int process_integer_literal(const char *token, YYSTYPE *lval, int
base);
static void addunicode(pg_wchar c, yyscan_t yyscanner);
@@ -395,19 +396,19 @@ hexdigit [0-9A-Fa-f]
octdigit [0-7]
bindigit [0-1]
-decinteger {decdigit}+
-hexinteger 0[xX]{hexdigit}+
-octinteger 0[oO]{octdigit}+
-bininteger 0[bB]{bindigit}+
+decinteger {decdigit}(_?{decdigit})*
+hexinteger 0[xX](_?{hexdigit})+
+octinteger 0[oO](_?{octdigit})+
+bininteger 0[bB](_?{bindigit})+
-hexfail 0[xX]
-octfail 0[oO]
-binfail 0[bB]
+hexfail 0[xX]_?
+octfail 0[oO]_?
+binfail 0[bB]_?
numeric (({decinteger}\.{decinteger}?)|(\.{decinteger}))
numericfail {decdigit}+\.\.
-real ({decinteger}|{numeric})[Ee][-+]?{decdigit}+
+real ({decinteger}|{numeric})[Ee][-+]?{decinteger}+
realfail ({decinteger}|{numeric})[Ee][-+]
decinteger_junk {decinteger}{ident_start}
@@ -1028,7 +1029,7 @@ other .
}
{numeric} {
SET_YYLLOC();
- yylval->str = pstrdup(yytext);
+ yylval->str = strip_underscores(yytext);
return FCONST;
}
{numericfail} {
@@ -1039,7 +1040,7 @@ other .
}
{real} {
SET_YYLLOC();
- yylval->str = pstrdup(yytext);
+ yylval->str = strip_underscores(yytext);
return FCONST;
}
{realfail} {
@@ -1357,6 +1358,30 @@ litbufdup(core_yyscan_t yyscanner)
return new;
}
+static char *
+strip_underscores(const char *in)
+{
+ if (strchr(in, '_'))
+ {
+ char *out = palloc(strlen(in));
+ const char *p1;
+ char *p2;
+
+ p1 = in;
+ p2 = out;
+ while (*p1)
+ {
+ if (*p1 != '_')
+ *p2++ = *p1;
+ p1++;
+ }
+ *p2 = '\0';
+ return out;
+ }
+ else
+ return pstrdup(in);
+}
+
/*
* Process {decinteger}, {hexinteger}, etc. Note this will also do the right
* thing with {numeric}, ie digits and a decimal point.
@@ -1367,6 +1392,24 @@ process_integer_literal(const char *token, YYSTYPE
*lval, int base)
int val;
char *endptr;
+ if (strchr(token, '_'))
+ {
+ char *newtoken = palloc(strlen(token));
+ const char *p1;
+ char *p2;
+
+ p1 = token;
+ p2 = newtoken;
+ while (*p1)
+ {
+ if (*p1 != '_')
+ *p2++ = *p1;
+ p1++;
+ }
+ *p2 = '\0';
+ token = newtoken;
+ }
+
errno = 0;
val = strtoint(base == 10 ? token : token + 2, &endptr, base);
if (*endptr != '\0' || errno == ERANGE)
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index 7cded73e6e..07f04d07cb 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -141,8 +141,16 @@ pg_strtoint16_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && isxdigit((unsigned char) *ptr))
+ while (*ptr && (isxdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT16_MIN / 16)))
goto out_of_range;
@@ -153,8 +161,16 @@ pg_strtoint16_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '7'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '7') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT16_MIN / 8)))
goto out_of_range;
@@ -165,8 +181,16 @@ pg_strtoint16_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '1'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '1') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT16_MIN / 2)))
goto out_of_range;
@@ -177,8 +201,20 @@ pg_strtoint16_safe(const char *s, Node *escontext)
{
firstdigit = ptr;
- while (*ptr && isdigit((unsigned char) *ptr))
+ while (*ptr && (isdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ /* underscore may not be first */
+ if (unlikely(ptr == firstdigit))
+ goto invalid_syntax;
+ /* not two in a row */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT16_MIN / 10)))
goto out_of_range;
@@ -190,6 +226,10 @@ pg_strtoint16_safe(const char *s, Node *escontext)
if (unlikely(ptr == firstdigit))
goto invalid_syntax;
+ /* underscore may not be last */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+
/* allow trailing whitespace, but not other trailing chars */
while (*ptr != '\0' && isspace((unsigned char) *ptr))
ptr++;
@@ -268,8 +308,16 @@ pg_strtoint32_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && isxdigit((unsigned char) *ptr))
+ while (*ptr && (isxdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT32_MIN / 16)))
goto out_of_range;
@@ -280,8 +328,16 @@ pg_strtoint32_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '7'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '7') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT32_MIN / 8)))
goto out_of_range;
@@ -292,8 +348,16 @@ pg_strtoint32_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '1'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '1') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT32_MIN / 2)))
goto out_of_range;
@@ -304,8 +368,20 @@ pg_strtoint32_safe(const char *s, Node *escontext)
{
firstdigit = ptr;
- while (*ptr && isdigit((unsigned char) *ptr))
+ while (*ptr && (isdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ /* underscore may not be first */
+ if (unlikely(ptr == firstdigit))
+ goto invalid_syntax;
+ /* not two in a row */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT32_MIN / 10)))
goto out_of_range;
@@ -317,6 +393,10 @@ pg_strtoint32_safe(const char *s, Node *escontext)
if (unlikely(ptr == firstdigit))
goto invalid_syntax;
+ /* underscore may not be last */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+
/* allow trailing whitespace, but not other trailing chars */
while (*ptr != '\0' && isspace((unsigned char) *ptr))
ptr++;
@@ -395,8 +475,16 @@ pg_strtoint64_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && isxdigit((unsigned char) *ptr))
+ while (*ptr && (isxdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT64_MIN / 16)))
goto out_of_range;
@@ -407,8 +495,16 @@ pg_strtoint64_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '7'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '7') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT64_MIN / 8)))
goto out_of_range;
@@ -419,8 +515,16 @@ pg_strtoint64_safe(const char *s, Node *escontext)
{
firstdigit = ptr += 2;
- while (*ptr && (*ptr >= '0' && *ptr <= '1'))
+ while (*ptr && ((*ptr >= '0' && *ptr <= '1') || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT64_MIN / 2)))
goto out_of_range;
@@ -431,8 +535,20 @@ pg_strtoint64_safe(const char *s, Node *escontext)
{
firstdigit = ptr;
- while (*ptr && isdigit((unsigned char) *ptr))
+ while (*ptr && (isdigit((unsigned char) *ptr) || *ptr == '_'))
{
+ if (*ptr == '_')
+ {
+ /* underscore may not be first */
+ if (unlikely(ptr == firstdigit))
+ goto invalid_syntax;
+ /* not two in a row */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+ ptr++;
+ continue;
+ }
+
if (unlikely(tmp > -(PG_INT64_MIN / 10)))
goto out_of_range;
@@ -444,6 +560,10 @@ pg_strtoint64_safe(const char *s, Node *escontext)
if (unlikely(ptr == firstdigit))
goto invalid_syntax;
+ /* underscore may not be last */
+ if (unlikely(*(ptr - 1) == '_'))
+ goto invalid_syntax;
+
/* allow trailing whitespace, but not other trailing chars */
while (*ptr != '\0' && isspace((unsigned char) *ptr))
ptr++;
diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l
index cb1fc52138..44f80e4db4 100644
--- a/src/fe_utils/psqlscan.l
+++ b/src/fe_utils/psqlscan.l
@@ -333,19 +333,19 @@ hexdigit [0-9A-Fa-f]
octdigit [0-7]
bindigit [0-1]
-decinteger {decdigit}+
-hexinteger 0[xX]{hexdigit}+
-octinteger 0[oO]{octdigit}+
-bininteger 0[bB]{bindigit}+
+decinteger {decdigit}(_?{decdigit})*
+hexinteger 0[xX](_?{hexdigit})+
+octinteger 0[oO](_?{octdigit})+
+bininteger 0[bB](_?{bindigit})+
-hexfail 0[xX]
-octfail 0[oO]
-binfail 0[bB]
+hexfail 0[xX]_?
+octfail 0[oO]_?
+binfail 0[bB]_?
numeric (({decinteger}\.{decinteger}?)|(\.{decinteger}))
numericfail {decdigit}+\.\.
-real ({decinteger}|{numeric})[Ee][-+]?{decdigit}+
+real ({decinteger}|{numeric})[Ee][-+]?{decinteger}
realfail ({decinteger}|{numeric})[Ee][-+]
decinteger_junk {decinteger}{ident_start}
diff --git a/src/interfaces/ecpg/preproc/pgc.l
b/src/interfaces/ecpg/preproc/pgc.l
index 2c09c6cb4f..2761ae34b8 100644
--- a/src/interfaces/ecpg/preproc/pgc.l
+++ b/src/interfaces/ecpg/preproc/pgc.l
@@ -361,19 +361,19 @@ hexdigit [0-9A-Fa-f]
octdigit [0-7]
bindigit [0-1]
-decinteger {decdigit}+
-hexinteger 0[xX]{hexdigit}+
-octinteger 0[oO]{octdigit}+
-bininteger 0[bB]{bindigit}+
+decinteger {decdigit}(_?{decdigit})*
+hexinteger 0[xX](_?{hexdigit})+
+octinteger 0[oO](_?{octdigit})+
+bininteger 0[bB](_?{bindigit})+
-hexfail 0[xX]
-octfail 0[oO]
-binfail 0[bB]
+hexfail 0[xX]_?
+octfail 0[oO]_?
+binfail 0[bB]_?
numeric (({decinteger}\.{decinteger}?)|(\.{decinteger}))
numericfail {decdigit}+\.\.
-real ({decinteger}|{numeric})[Ee][-+]?{decdigit}+
+real ({decinteger}|{numeric})[Ee][-+]?{decinteger}
realfail ({decinteger}|{numeric})[Ee][-+]
decinteger_junk {decinteger}{ident_start}
diff --git a/src/pl/plpgsql/src/expected/plpgsql_trap.out
b/src/pl/plpgsql/src/expected/plpgsql_trap.out
index 90cf6c2895..62d1679c28 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_trap.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_trap.out
@@ -141,7 +141,7 @@ begin
declare x int;
begin
-- we assume this will take longer than 1 second:
- select count(*) into x from generate_series(1, 1000000000000);
+ select count(*) into x from generate_series(1, 1_000_000_000_000);
exception
when others then
raise notice 'caught others?';
diff --git a/src/pl/plpgsql/src/sql/plpgsql_trap.sql
b/src/pl/plpgsql/src/sql/plpgsql_trap.sql
index c6c1ad894b..5459b347e7 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_trap.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_trap.sql
@@ -88,7 +88,7 @@
declare x int;
begin
-- we assume this will take longer than 1 second:
- select count(*) into x from generate_series(1, 1000000000000);
+ select count(*) into x from generate_series(1, 1_000_000_000_000);
exception
when others then
raise notice 'caught others?';
diff --git a/src/test/regress/expected/int2.out
b/src/test/regress/expected/int2.out
index 08c333b75a..73b4ee023c 100644
--- a/src/test/regress/expected/int2.out
+++ b/src/test/regress/expected/int2.out
@@ -440,3 +440,47 @@ SELECT int2 '-0x8001';
ERROR: value "-0x8001" is out of range for type smallint
LINE 1: SELECT int2 '-0x8001';
^
+-- underscores
+SELECT int2 '1_000';
+ int2
+------
+ 1000
+(1 row)
+
+SELECT int2 '1_2_3';
+ int2
+------
+ 123
+(1 row)
+
+SELECT int2 '0xE_FF';
+ int2
+------
+ 3839
+(1 row)
+
+SELECT int2 '0o2_73';
+ int2
+------
+ 187
+(1 row)
+
+SELECT int2 '0b_10_0101';
+ int2
+------
+ 37
+(1 row)
+
+-- error cases
+SELECT int2 '_100';
+ERROR: invalid input syntax for type smallint: "_100"
+LINE 1: SELECT int2 '_100';
+ ^
+SELECT int2 '100_';
+ERROR: invalid input syntax for type smallint: "100_"
+LINE 1: SELECT int2 '100_';
+ ^
+SELECT int2 '10__000';
+ERROR: invalid input syntax for type smallint: "10__000"
+LINE 1: SELECT int2 '10__000';
+ ^
diff --git a/src/test/regress/expected/int4.out
b/src/test/regress/expected/int4.out
index 8386c7cdff..9c20574ca5 100644
--- a/src/test/regress/expected/int4.out
+++ b/src/test/regress/expected/int4.out
@@ -548,3 +548,47 @@ SELECT int4 '-0x80000001';
ERROR: value "-0x80000001" is out of range for type integer
LINE 1: SELECT int4 '-0x80000001';
^
+-- underscores
+SELECT int4 '1_000_000';
+ int4
+---------
+ 1000000
+(1 row)
+
+SELECT int4 '1_2_3';
+ int4
+------
+ 123
+(1 row)
+
+SELECT int4 '0x1EEE_FFFF';
+ int4
+-----------
+ 518979583
+(1 row)
+
+SELECT int4 '0o2_73';
+ int4
+------
+ 187
+(1 row)
+
+SELECT int4 '0b_10_0101';
+ int4
+------
+ 37
+(1 row)
+
+-- error cases
+SELECT int4 '_100';
+ERROR: invalid input syntax for type integer: "_100"
+LINE 1: SELECT int4 '_100';
+ ^
+SELECT int4 '100_';
+ERROR: invalid input syntax for type integer: "100_"
+LINE 1: SELECT int4 '100_';
+ ^
+SELECT int4 '100__000';
+ERROR: invalid input syntax for type integer: "100__000"
+LINE 1: SELECT int4 '100__000';
+ ^
diff --git a/src/test/regress/expected/int8.out
b/src/test/regress/expected/int8.out
index 5b62b51be9..d9dca64e88 100644
--- a/src/test/regress/expected/int8.out
+++ b/src/test/regress/expected/int8.out
@@ -1044,3 +1044,47 @@ SELECT int8 '-0x8000000000000001';
ERROR: value "-0x8000000000000001" is out of range for type bigint
LINE 1: SELECT int8 '-0x8000000000000001';
^
+-- underscores
+SELECT int8 '1_000_000';
+ int8
+---------
+ 1000000
+(1 row)
+
+SELECT int8 '1_2_3';
+ int8
+------
+ 123
+(1 row)
+
+SELECT int8 '0x1EEE_FFFF';
+ int8
+-----------
+ 518979583
+(1 row)
+
+SELECT int8 '0o2_73';
+ int8
+------
+ 187
+(1 row)
+
+SELECT int8 '0b_10_0101';
+ int8
+------
+ 37
+(1 row)
+
+-- error cases
+SELECT int8 '_100';
+ERROR: invalid input syntax for type bigint: "_100"
+LINE 1: SELECT int8 '_100';
+ ^
+SELECT int8 '100_';
+ERROR: invalid input syntax for type bigint: "100_"
+LINE 1: SELECT int8 '100_';
+ ^
+SELECT int8 '100__000';
+ERROR: invalid input syntax for type bigint: "100__000"
+LINE 1: SELECT int8 '100__000';
+ ^
diff --git a/src/test/regress/expected/numerology.out
b/src/test/regress/expected/numerology.out
index 15cd6b1672..6b9b089cb5 100644
--- a/src/test/regress/expected/numerology.out
+++ b/src/test/regress/expected/numerology.out
@@ -166,10 +166,6 @@ SELECT 0x0o;
ERROR: trailing junk after numeric literal at or near "0x0o"
LINE 1: SELECT 0x0o;
^
-SELECT 1_2_3;
-ERROR: trailing junk after numeric literal at or near "1_"
-LINE 1: SELECT 1_2_3;
- ^
SELECT 0.a;
ERROR: trailing junk after numeric literal at or near "0.a"
LINE 1: SELECT 0.a;
@@ -234,6 +230,94 @@ SELECT 0x0y;
ERROR: trailing junk after numeric literal at or near "0x0y"
LINE 1: SELECT 0x0y;
^
+-- underscores
+SELECT 1_000_000;
+ ?column?
+----------
+ 1000000
+(1 row)
+
+SELECT 1_2_3;
+ ?column?
+----------
+ 123
+(1 row)
+
+SELECT 0x1EEE_FFFF;
+ ?column?
+-----------
+ 518979583
+(1 row)
+
+SELECT 0o2_73;
+ ?column?
+----------
+ 187
+(1 row)
+
+SELECT 0b_10_0101;
+ ?column?
+----------
+ 37
+(1 row)
+
+SELECT 1_000.000_005;
+ ?column?
+-------------
+ 1000.000005
+(1 row)
+
+SELECT 1_000.;
+ ?column?
+----------
+ 1000
+(1 row)
+
+SELECT .000_005;
+ ?column?
+----------
+ 0.000005
+(1 row)
+
+SELECT 1_000.5e0_1;
+ ?column?
+----------
+ 10005
+(1 row)
+
+-- error cases
+SELECT _100;
+ERROR: column "_100" does not exist
+LINE 1: SELECT _100;
+ ^
+SELECT 100_;
+ERROR: trailing junk after numeric literal at or near "100_"
+LINE 1: SELECT 100_;
+ ^
+SELECT 100__000;
+ERROR: trailing junk after numeric literal at or near "100_"
+LINE 1: SELECT 100__000;
+ ^
+SELECT _1_000.5;
+ERROR: syntax error at or near ".5"
+LINE 1: SELECT _1_000.5;
+ ^
+SELECT 1_000_.5;
+ERROR: trailing junk after numeric literal at or near "1_000_"
+LINE 1: SELECT 1_000_.5;
+ ^
+SELECT 1_000._5;
+ERROR: trailing junk after numeric literal at or near "1_000._"
+LINE 1: SELECT 1_000._5;
+ ^
+SELECT 1_000.5_;
+ERROR: trailing junk after numeric literal at or near "1_000.5_"
+LINE 1: SELECT 1_000.5_;
+ ^
+SELECT 1_000.5e_1;
+ERROR: trailing junk after numeric literal at or near "1_000.5e"
+LINE 1: SELECT 1_000.5e_1;
+ ^
--
-- Test implicit type conversions
-- This fails for Postgres v6.1 (and earlier?)
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 7555764c77..d700c00629 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1503,7 +1503,7 @@ explain (costs off) select * from like_op_noprune where a
like '%BC';
create table lparted_by_int2 (a smallint) partition by list (a);
create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
create table lparted_by_int2_16384 partition of lparted_by_int2 for values in
(16384);
-explain (costs off) select * from lparted_by_int2 where a = 100000000000000;
+explain (costs off) select * from lparted_by_int2 where a =
100_000_000_000_000;
QUERY PLAN
--------------------------
Result
@@ -1514,7 +1514,7 @@ create table rparted_by_int2 (a smallint) partition by
range (a);
create table rparted_by_int2_1 partition of rparted_by_int2 for values from
(1) to (10);
create table rparted_by_int2_16384 partition of rparted_by_int2 for values
from (10) to (16384);
-- all partitions pruned
-explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
+explain (costs off) select * from rparted_by_int2 where a >
100_000_000_000_000;
QUERY PLAN
--------------------------
Result
@@ -1523,7 +1523,7 @@ explain (costs off) select * from rparted_by_int2 where a
> 100000000000000;
create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values
from (16384) to (maxvalue);
-- all partitions but rparted_by_int2_maxvalue pruned
-explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
+explain (costs off) select * from rparted_by_int2 where a >
100_000_000_000_000;
QUERY PLAN
------------------------------------------------------
Seq Scan on rparted_by_int2_maxvalue rparted_by_int2
diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql
index a812235ee5..ce8ac97963 100644
--- a/src/test/regress/sql/int2.sql
+++ b/src/test/regress/sql/int2.sql
@@ -141,3 +141,17 @@
SELECT int2 '-0o100001';
SELECT int2 '-0x8000';
SELECT int2 '-0x8001';
+
+
+-- underscores
+
+SELECT int2 '1_000';
+SELECT int2 '1_2_3';
+SELECT int2 '0xE_FF';
+SELECT int2 '0o2_73';
+SELECT int2 '0b_10_0101';
+
+-- error cases
+SELECT int2 '_100';
+SELECT int2 '100_';
+SELECT int2 '10__000';
diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql
index 9e6a40408a..146963edfb 100644
--- a/src/test/regress/sql/int4.sql
+++ b/src/test/regress/sql/int4.sql
@@ -196,3 +196,17 @@
SELECT int4 '-0o20000000001';
SELECT int4 '-0x80000000';
SELECT int4 '-0x80000001';
+
+
+-- underscores
+
+SELECT int4 '1_000_000';
+SELECT int4 '1_2_3';
+SELECT int4 '0x1EEE_FFFF';
+SELECT int4 '0o2_73';
+SELECT int4 '0b_10_0101';
+
+-- error cases
+SELECT int4 '_100';
+SELECT int4 '100_';
+SELECT int4 '100__000';
diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql
index 06f273ed58..c85717c072 100644
--- a/src/test/regress/sql/int8.sql
+++ b/src/test/regress/sql/int8.sql
@@ -277,3 +277,17 @@
SELECT int8 '-0o1000000000000000000001';
SELECT int8 '-0x8000000000000000';
SELECT int8 '-0x8000000000000001';
+
+
+-- underscores
+
+SELECT int8 '1_000_000';
+SELECT int8 '1_2_3';
+SELECT int8 '0x1EEE_FFFF';
+SELECT int8 '0o2_73';
+SELECT int8 '0b_10_0101';
+
+-- error cases
+SELECT int8 '_100';
+SELECT int8 '100_';
+SELECT int8 '100__000';
diff --git a/src/test/regress/sql/numerology.sql
b/src/test/regress/sql/numerology.sql
index 310d9e5766..1941c58e68 100644
--- a/src/test/regress/sql/numerology.sql
+++ b/src/test/regress/sql/numerology.sql
@@ -45,7 +45,6 @@
-- error cases
SELECT 123abc;
SELECT 0x0o;
-SELECT 1_2_3;
SELECT 0.a;
SELECT 0.0a;
SELECT .0a;
@@ -66,6 +65,29 @@
SELECT 1x;
SELECT 0x0y;
+-- underscores
+SELECT 1_000_000;
+SELECT 1_2_3;
+SELECT 0x1EEE_FFFF;
+SELECT 0o2_73;
+SELECT 0b_10_0101;
+
+SELECT 1_000.000_005;
+SELECT 1_000.;
+SELECT .000_005;
+SELECT 1_000.5e0_1;
+
+-- error cases
+SELECT _100;
+SELECT 100_;
+SELECT 100__000;
+
+SELECT _1_000.5;
+SELECT 1_000_.5;
+SELECT 1_000._5;
+SELECT 1_000.5_;
+SELECT 1_000.5e_1;
+
--
-- Test implicit type conversions
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index d70bd8610c..fb0583f924 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -283,16 +283,16 @@ CREATE TABLE part_rev (b INT, c INT, a INT);
create table lparted_by_int2 (a smallint) partition by list (a);
create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
create table lparted_by_int2_16384 partition of lparted_by_int2 for values in
(16384);
-explain (costs off) select * from lparted_by_int2 where a = 100000000000000;
+explain (costs off) select * from lparted_by_int2 where a =
100_000_000_000_000;
create table rparted_by_int2 (a smallint) partition by range (a);
create table rparted_by_int2_1 partition of rparted_by_int2 for values from
(1) to (10);
create table rparted_by_int2_16384 partition of rparted_by_int2 for values
from (10) to (16384);
-- all partitions pruned
-explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
+explain (costs off) select * from rparted_by_int2 where a >
100_000_000_000_000;
create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values
from (16384) to (maxvalue);
-- all partitions but rparted_by_int2_maxvalue pruned
-explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
+explain (costs off) select * from rparted_by_int2 where a >
100_000_000_000_000;
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp,
coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
base-commit: 63c844a0a5d70cdbd6ae0470d582d39e75ad8d66
--
2.39.0
