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

1 files changed, 34 insertions, 0 deletions

diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 7e17c28d51..14b4acfe12 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1033,6 +1033,40 @@ INSERT INTO num_input_test(n1) VALUES ('+ infinity');
 SELECT * FROM num_input_test;
 
 --
+-- Test precision and scale typemods
+--
+
+CREATE TABLE num_typemod_test (
+  millions numeric(3, -6),
+  thousands numeric(3, -3),
+  units numeric(3, 0),
+  thousandths numeric(3, 3),
+  millionths numeric(3, 6)
+);
+\d num_typemod_test
+
+-- rounding of valid inputs
+INSERT INTO num_typemod_test VALUES (123456, 123, 0.123, 0.000123, 0.000000123);
+INSERT INTO num_typemod_test VALUES (654321, 654, 0.654, 0.000654, 0.000000654);
+INSERT INTO num_typemod_test VALUES (2345678, 2345, 2.345, 0.002345, 0.000002345);
+INSERT INTO num_typemod_test VALUES (7654321, 7654, 7.654, 0.007654, 0.000007654);
+INSERT INTO num_typemod_test VALUES (12345678, 12345, 12.345, 0.012345, 0.000012345);
+INSERT INTO num_typemod_test VALUES (87654321, 87654, 87.654, 0.087654, 0.000087654);
+INSERT INTO num_typemod_test VALUES (123456789, 123456, 123.456, 0.123456, 0.000123456);
+INSERT INTO num_typemod_test VALUES (987654321, 987654, 987.654, 0.987654, 0.000987654);
+INSERT INTO num_typemod_test VALUES ('NaN', 'NaN', 'NaN', 'NaN', 'NaN');
+
+SELECT scale(millions), * FROM num_typemod_test ORDER BY millions;
+
+-- invalid inputs
+INSERT INTO num_typemod_test (millions) VALUES ('inf');
+INSERT INTO num_typemod_test (millions) VALUES (999500000);
+INSERT INTO num_typemod_test (thousands) VALUES (999500);
+INSERT INTO num_typemod_test (units) VALUES (999.5);
+INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
+INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
+
+--
 -- Test some corner cases for multiplication
 --