Allow numeric scale to be negative or greater than precision.

Formerly, when specifying NUMERIC(precision, scale), the scale had to
be in the range [0, precision], which was per SQL spec. This commit
extends the range of allowed scales to [-1000, 1000], independent of
the precision (whose valid range remains [1, 1000]).

A negative scale implies rounding before the decimal point. For
example, a column might be declared with a scale of -3 to round values
to the nearest thousand. Note that the display scale remains
non-negative, so in this case the display scale will be zero, and all
digits before the decimal point will be displayed.

A scale greater than the precision supports fractional values with
zeros immediately after the decimal point.

Take the opportunity to tidy up the code that packs, unpacks and
validates the contents of a typmod integer, encapsulating it in a
small set of new inline functions.

Bump the catversion because the allowed contents of atttypmod have
changed for numeric columns. This isn't a change that requires a
re-initdb, but negative scale values in the typmod would confuse old
backends.

Dean Rasheed, with additional improvements by Tom Lane. Reviewed by
Tom Lane.

Discussion: https://postgr.es/m/CAEZATCWdNLgpKihmURF8nfofP0RFtAKJ7ktY6GcZOPnMfUoRqA@mail.gmail.com

2 files changed, 14 insertions, 4 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 0a4f4abdb39..549c8991442 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202107181
+#define CATALOG_VERSION_NO	202107261
 
 #endif
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index dfc8688ca22..91ac5ed7714 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -17,12 +17,22 @@
 #include "fmgr.h"
 
 /*
- * Limit on the precision (and hence scale) specifiable in a NUMERIC typmod.
- * Note that the implementation limit on the length of a numeric value is
- * much larger --- beware of what you use this for!
+ * Limits on the precision and scale specifiable in a NUMERIC typmod.  The
+ * precision is strictly positive, but the scale may be positive or negative.
+ * A negative scale implies rounding before the decimal point.
+ *
+ * Note that the minimum display scale defined below is zero --- we always
+ * display all digits before the decimal point, even when the scale is
+ * negative.
+ *
+ * Note that the implementation limits on the precision and display scale of a
+ * numeric value are much larger --- beware of what you use these for!
  */
 #define NUMERIC_MAX_PRECISION		1000
 
+#define NUMERIC_MIN_SCALE			(-1000)
+#define NUMERIC_MAX_SCALE			1000
+
 /*
  * Internal limits on the scales chosen for calculation results
  */