WIP: Relaxing the constraints on numeric scale

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index c473d6a..97e4cdf
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -545,8 +545,8 @@
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
 </programlisting>
-     The precision must be positive, the scale zero or positive.
-     Alternatively:
+     The precision must be positive, the scale may be positive or negative
+     (see below).  Alternatively:
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>)
 </programlisting>
@@ -578,11 +578,41 @@ NUMERIC
     <para>
      If the scale of a value to be stored is greater than the declared
      scale of the column, the system will round the value to the specified
-     number of fractional digits.  Then, if the number of digits to the
-     left of the decimal point exceeds the declared precision minus the
-     declared scale, an error is raised.
+     number of fractional digits.  If the declared scale of the column is
+     negative, the value will be rounded to the left of the decimal point.
+     If, after rounding, the number of digits to the left of the decimal point
+     exceeds the declared precision minus the declared scale, an error is
+     raised.  Similarly, if the declared scale exceeds the declared precision
+     and the number of zero digits to the right of the decimal point is less
+     than the declared scale minus the declared precision, an error is raised.
+     For example, a column declared as
+<programlisting>
+NUMERIC(3, 1)
+</programlisting>
+     will round values to 1 decimal place and be able to store values between
+     -99.9 and 99.9, inclusive.  A column declared as
+<programlisting>
+NUMERIC(2, -3)
+</programlisting>
+     will round values to the nearest thousand and be able to store values
+     between -99000 and 99000, inclusive.  A column declared as
+<programlisting>
+NUMERIC(3, 5)
+</programlisting>
+     will round values to 5 decimal places and be able to store values between
+     -0.00999 and 0.00999, inclusive.
     </para>
 
+    <note>
+     <para>
+      The scale in a <type>NUMERIC</type> type declaration may be any value in
+      the range -1000 to 1000.  (The <acronym>SQL</acronym> standard requires
+      the scale to be in the range 0 to <replaceable>precision</replaceable>.
+      Using values outside this range may not be portable to other database
+      systems.)
+     </para>
+    </note>
+
     <para>
      Numeric values are physically stored without any extra leading or
      trailing zeroes.  Thus, the declared precision and scale of a column
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index eb78f0b..2001d75
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -250,6 +250,17 @@ struct NumericData
 	 | ((n)->choice.n_short.n_header & NUMERIC_SHORT_WEIGHT_MASK)) \
 	: ((n)->choice.n_long.n_weight))
 
+/*
+ * Pack the numeric precision and scale in the typmod value.  The upper 16
+ * bits are used for the precision, and the lower 16 bits for the scale.  Note
+ * that the scale may be negative, so use sign extension when unpacking it.
+ */
+
+#define MAKE_TYPMOD(p, s) ((((p) << 16) | ((s) & 0xffff)) + VARHDRSZ)
+
+#define TYPMOD_PRECISION(t) ((((t) - VARHDRSZ) >> 16) & 0xffff)
+#define TYPMOD_SCALE(t) ((int32) ((int16) (((t) - VARHDRSZ) & 0xffff)))
+
 /* ----------
  * NumericVar is the format we use for arithmetic.  The digit-array part
  * is the same as the NumericData storage format, but the header is more
@@ -826,7 +837,7 @@ numeric_maximum_size(int32 typmod)
 		return -1;
 
 	/* precision (ie, max # of digits) is in upper bits of typmod */
-	precision = ((typmod - VARHDRSZ) >> 16) & 0xffff;
+	precision = TYPMOD_PRECISION(typmod);
 
 	/*
 	 * This formula computes the maximum number of NumericDigits we could need
@@ -1080,10 +1091,10 @@ numeric_support(PG_FUNCTION_ARGS)
 			Node	   *source = (Node *) linitial(expr->args);
 			int32		old_typmod = exprTypmod(source);
 			int32		new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
-			int32		old_scale = (old_typmod - VARHDRSZ) & 0xffff;
-			int32		new_scale = (new_typmod - VARHDRSZ) & 0xffff;
-			int32		old_precision = (old_typmod - VARHDRSZ) >> 16 & 0xffff;
-			int32		new_precision = (new_typmod - VARHDRSZ) >> 16 & 0xffff;
+			int32		old_scale = TYPMOD_SCALE(old_typmod);
+			int32		new_scale = TYPMOD_SCALE(new_typmod);
+			int32		old_precision = TYPMOD_PRECISION(old_typmod);
+			int32		new_precision = TYPMOD_PRECISION(new_typmod);
 
 			/*
 			 * If new_typmod < VARHDRSZ, the destination is unconstrained;
@@ -1115,11 +1126,11 @@ numeric		(PG_FUNCTION_ARGS)
 	Numeric		num = PG_GETARG_NUMERIC(0);
 	int32		typmod = PG_GETARG_INT32(1);
 	Numeric		new;
-	int32		tmp_typmod;
 	int			precision;
 	int			scale;
 	int			ddigits;
 	int			maxdigits;
+	int			dscale;
 	NumericVar	var;
 
 	/*
@@ -1142,11 +1153,13 @@ numeric		(PG_FUNCTION_ARGS)
 	/*
 	 * Get the precision and scale out of the typmod value
 	 */
-	tmp_typmod = typmod - VARHDRSZ;
-	precision = (tmp_typmod >> 16) & 0xffff;
-	scale = tmp_typmod & 0xffff;
+	precision = TYPMOD_PRECISION(typmod);
+	scale = TYPMOD_SCALE(typmod);
 	maxdigits = precision - scale;
 
+	/* The target display scale is non-negative */
+	dscale = Max(scale, 0);
+
 	/*
 	 * If the number is certainly in bounds and due to the target scale no
 	 * rounding could be necessary, just make a copy of the input and modify
@@ -1156,17 +1169,17 @@ numeric		(PG_FUNCTION_ARGS)
 	 */
 	ddigits = (NUMERIC_WEIGHT(num) + 1) * DEC_DIGITS;
 	if (ddigits <= maxdigits && scale >= NUMERIC_DSCALE(num)
-		&& (NUMERIC_CAN_BE_SHORT(scale, NUMERIC_WEIGHT(num))
+		&& (NUMERIC_CAN_BE_SHORT(dscale, NUMERIC_WEIGHT(num))
 			|| !NUMERIC_IS_SHORT(num)))
 	{
 		new = duplicate_numeric(num);
 		if (NUMERIC_IS_SHORT(num))
 			new->choice.n_short.n_header =
 				(num->choice.n_short.n_header & ~NUMERIC_SHORT_DSCALE_MASK)
-				| (scale << NUMERIC_SHORT_DSCALE_SHIFT);
+				| (dscale << NUMERIC_SHORT_DSCALE_SHIFT);
 		else
 			new->choice.n_long.n_sign_dscale = NUMERIC_SIGN(new) |
-				((uint16) scale & NUMERIC_DSCALE_MASK);
+				((uint16) dscale & NUMERIC_DSCALE_MASK);
 		PG_RETURN_NUMERIC(new);
 	}
 
@@ -1202,12 +1215,12 @@ numerictypmodin(PG_FUNCTION_ARGS)
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("NUMERIC precision %d must be between 1 and %d",
 							tl[0], NUMERIC_MAX_PRECISION)));
-		if (tl[1] < 0 || tl[1] > tl[0])
+		if (tl[1] < NUMERIC_MIN_SCALE || tl[1] > NUMERIC_MAX_SCALE)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("NUMERIC scale %d must be between 0 and precision %d",
-							tl[1], tl[0])));
-		typmod = ((tl[0] << 16) | tl[1]) + VARHDRSZ;
+					 errmsg("NUMERIC scale %d must be between %d and %d",
+							tl[1], NUMERIC_MIN_SCALE, NUMERIC_MAX_SCALE)));
+		typmod = MAKE_TYPMOD(tl[0], tl[1]);
 	}
 	else if (n == 1)
 	{
@@ -1217,7 +1230,7 @@ numerictypmodin(PG_FUNCTION_ARGS)
 					 errmsg("NUMERIC precision %d must be between 1 and %d",
 							tl[0], NUMERIC_MAX_PRECISION)));
 		/* scale defaults to zero */
-		typmod = (tl[0] << 16) + VARHDRSZ;
+		typmod = MAKE_TYPMOD(tl[0], 0);
 	}
 	else
 	{
@@ -1238,8 +1251,8 @@ numerictypmodout(PG_FUNCTION_ARGS)
 
 	if (typmod >= 0)
 		snprintf(res, 64, "(%d,%d)",
-				 ((typmod - VARHDRSZ) >> 16) & 0xffff,
-				 (typmod - VARHDRSZ) & 0xffff);
+				 TYPMOD_PRECISION(typmod),
+				 TYPMOD_SCALE(typmod));
 	else
 		*res = '\0';
 
@@ -7445,14 +7458,17 @@ apply_typmod(NumericVar *var, int32 typm
 	if (typmod < (int32) (VARHDRSZ))
 		return;
 
-	typmod -= VARHDRSZ;
-	precision = (typmod >> 16) & 0xffff;
-	scale = typmod & 0xffff;
+	precision = TYPMOD_PRECISION(typmod);
+	scale = TYPMOD_SCALE(typmod);
 	maxdigits = precision - scale;
 
 	/* Round to target scale (and set var->dscale) */
 	round_var(var, scale);
 
+	/* but don't allow var->dscale to be negative */
+	if (var->dscale < 0)
+		var->dscale = 0;
+
 	/*
 	 * Check for overflow - note we can't do this before rounding, because
 	 * rounding could raise the weight.  Also note that the var's weight could
@@ -7530,9 +7546,8 @@ apply_typmod_special(Numeric num, int32
 	if (typmod < (int32) (VARHDRSZ))
 		return;
 
-	typmod -= VARHDRSZ;
-	precision = (typmod >> 16) & 0xffff;
-	scale = typmod & 0xffff;
+	precision = TYPMOD_PRECISION(typmod);
+	scale = TYPMOD_SCALE(typmod);
 
 	ereport(ERROR,
 			(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
new file mode 100644
index dfc8688..821c4ac
--- 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
  */
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
new file mode 100644
index 30a5642..4337dba
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2119,6 +2119,70 @@ SELECT * FROM num_input_test;
 (13 rows)
 
 --
+-- 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
+               Table "public.num_typemod_test"
+   Column    |     Type      | Collation | Nullable | Default 
+-------------+---------------+-----------+----------+---------
+ millions    | numeric(3,-6) |           |          | 
+ thousands   | numeric(3,-3) |           |          | 
+ units       | numeric(3,0)  |           |          | 
+ thousandths | numeric(3,3)  |           |          | 
+ millionths  | numeric(3,6)  |           |          | 
+
+-- 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;
+ scale | millions  | thousands | units | thousandths | millionths 
+-------+-----------+-----------+-------+-------------+------------
+     0 |         0 |         0 |     0 |       0.000 |   0.000000
+     0 |   1000000 |      1000 |     1 |       0.001 |   0.000001
+     0 |   2000000 |      2000 |     2 |       0.002 |   0.000002
+     0 |   8000000 |      8000 |     8 |       0.008 |   0.000008
+     0 |  12000000 |     12000 |    12 |       0.012 |   0.000012
+     0 |  88000000 |     88000 |    88 |       0.088 |   0.000088
+     0 | 123000000 |    123000 |   123 |       0.123 |   0.000123
+     0 | 988000000 |    988000 |   988 |       0.988 |   0.000988
+       |       NaN |       NaN |   NaN |         NaN |        NaN
+(9 rows)
+
+-- invalid inputs
+INSERT INTO num_typemod_test (millions) VALUES ('inf');
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -6 cannot hold an infinite value.
+INSERT INTO num_typemod_test (millions) VALUES (999500000);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -6 must round to an absolute value less than 10^9.
+INSERT INTO num_typemod_test (thousands) VALUES (999500);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -3 must round to an absolute value less than 10^6.
+INSERT INTO num_typemod_test (units) VALUES (999.5);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.
+INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 3 must round to an absolute value less than 1.
+INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 6 must round to an absolute value less than 10^-3.
+DROP TABLE num_typemod_test;
+--
 -- Test some corner cases for multiplication
 --
 select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
new file mode 100644
index db812c8..5740ad9
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1033,6 +1033,42 @@ INSERT INTO num_input_test(n1) VALUES ('
 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);
+
+DROP TABLE num_typemod_test;
+
+--
 -- Test some corner cases for multiplication
 --
 

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index e016f96..6abda2f
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -545,8 +545,8 @@
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
 </programlisting>
-     The precision must be positive, the scale zero or positive.
-     Alternatively:
+     The precision must be positive, the scale may be positive or negative
+     (see below).  Alternatively:
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>)
 </programlisting>
@@ -578,11 +578,41 @@ NUMERIC
     <para>
      If the scale of a value to be stored is greater than the declared
      scale of the column, the system will round the value to the specified
-     number of fractional digits.  Then, if the number of digits to the
-     left of the decimal point exceeds the declared precision minus the
-     declared scale, an error is raised.
+     number of fractional digits.  If the declared scale of the column is
+     negative, the value will be rounded to the left of the decimal point.
+     If, after rounding, the number of digits to the left of the decimal point
+     exceeds the declared precision minus the declared scale, an error is
+     raised.  Similarly, if the declared scale exceeds the declared precision
+     and the number of zero digits to the right of the decimal point is less
+     than the declared scale minus the declared precision, an error is raised.
+     For example, a column declared as
+<programlisting>
+NUMERIC(3, 1)
+</programlisting>
+     will round values to 1 decimal place and be able to store values between
+     -99.9 and 99.9, inclusive.  A column declared as
+<programlisting>
+NUMERIC(2, -3)
+</programlisting>
+     will round values to the nearest thousand and be able to store values
+     between -99000 and 99000, inclusive.  A column declared as
+<programlisting>
+NUMERIC(3, 5)
+</programlisting>
+     will round values to 5 decimal places and be able to store values between
+     -0.00999 and 0.00999, inclusive.
     </para>
 
+    <note>
+     <para>
+      The scale in a <type>NUMERIC</type> type declaration may be any value in
+      the range -1000 to 1000.  (The <acronym>SQL</acronym> standard requires
+      the scale to be in the range 0 to <replaceable>precision</replaceable>.
+      Using values outside this range may not be portable to other database
+      systems.)
+     </para>
+    </note>
+
     <para>
      Numeric values are physically stored without any extra leading or
      trailing zeroes.  Thus, the declared precision and scale of a column
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index 2a0f68f..46cb37c
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -816,6 +816,52 @@ numeric_is_integral(Numeric num)
 }
 
 /*
+ * make_numeric_typmod() -
+ *
+ *	Pack numeric precision and scale values into a typmod.  The upper 16 bits
+ *	are used for the precision (though actually not all these bits are needed,
+ *	since the maximum allowed precision is 1000).  The lower 16 bits are for
+ *	the scale, but since the scale is constrained to the range [-1000, 1000],
+ *	we use just the lower 11 of those 16 bits, and leave the remaining 5 bits
+ *	unset, for possible future use.
+ *
+ *	For purely historical reasons VARHDRSZ is then added to the result, thus
+ *	the unused space in the upper 16 bits is not all as freely available as it
+ *	might seem.
+ */
+static inline int
+make_numeric_typmod(int precision, int scale)
+{
+	return ((precision << 16) | (scale & 0x7ff)) + VARHDRSZ;
+}
+
+/*
+ * numeric_typmod_precision() -
+ *
+ *	Extract the precision from a numeric typmod --- see make_numeric_typmod().
+ */
+static inline int
+numeric_typmod_precision(int typmod)
+{
+	return ((typmod - VARHDRSZ) >> 16) & 0xffff;
+}
+
+/*
+ * numeric_typmod_scale() -
+ *
+ *	Extract the scale from a numeric typmod --- see make_numeric_typmod().
+ *
+ *	Note that the scale may be negative, so we must do sign extension when
+ *	unpacking it.  We do this using the bit hack (x^1024)-1024, which sign
+ *	extends an 11-bit two's complement number x.
+ */
+static inline int
+numeric_typmod_scale(int typmod)
+{
+	return (((typmod - VARHDRSZ) & 0x7ff) ^ 1024) - 1024;
+}
+
+/*
  * numeric_maximum_size() -
  *
  *	Maximum size of a numeric with given typmod, or -1 if unlimited/unknown.
@@ -830,7 +876,7 @@ numeric_maximum_size(int32 typmod)
 		return -1;
 
 	/* precision (ie, max # of digits) is in upper bits of typmod */
-	precision = ((typmod - VARHDRSZ) >> 16) & 0xffff;
+	precision = numeric_typmod_precision(typmod);
 
 	/*
 	 * This formula computes the maximum number of NumericDigits we could need
@@ -1084,10 +1130,10 @@ numeric_support(PG_FUNCTION_ARGS)
 			Node	   *source = (Node *) linitial(expr->args);
 			int32		old_typmod = exprTypmod(source);
 			int32		new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
-			int32		old_scale = (old_typmod - VARHDRSZ) & 0xffff;
-			int32		new_scale = (new_typmod - VARHDRSZ) & 0xffff;
-			int32		old_precision = (old_typmod - VARHDRSZ) >> 16 & 0xffff;
-			int32		new_precision = (new_typmod - VARHDRSZ) >> 16 & 0xffff;
+			int32		old_scale = numeric_typmod_scale(old_typmod);
+			int32		new_scale = numeric_typmod_scale(new_typmod);
+			int32		old_precision = numeric_typmod_precision(old_typmod);
+			int32		new_precision = numeric_typmod_precision(new_typmod);
 
 			/*
 			 * If new_typmod < VARHDRSZ, the destination is unconstrained;
@@ -1119,11 +1165,11 @@ numeric		(PG_FUNCTION_ARGS)
 	Numeric		num = PG_GETARG_NUMERIC(0);
 	int32		typmod = PG_GETARG_INT32(1);
 	Numeric		new;
-	int32		tmp_typmod;
 	int			precision;
 	int			scale;
 	int			ddigits;
 	int			maxdigits;
+	int			dscale;
 	NumericVar	var;
 
 	/*
@@ -1146,11 +1192,13 @@ numeric		(PG_FUNCTION_ARGS)
 	/*
 	 * Get the precision and scale out of the typmod value
 	 */
-	tmp_typmod = typmod - VARHDRSZ;
-	precision = (tmp_typmod >> 16) & 0xffff;
-	scale = tmp_typmod & 0xffff;
+	precision = numeric_typmod_precision(typmod);
+	scale = numeric_typmod_scale(typmod);
 	maxdigits = precision - scale;
 
+	/* The target display scale is non-negative */
+	dscale = Max(scale, 0);
+
 	/*
 	 * If the number is certainly in bounds and due to the target scale no
 	 * rounding could be necessary, just make a copy of the input and modify
@@ -1160,17 +1208,17 @@ numeric		(PG_FUNCTION_ARGS)
 	 */
 	ddigits = (NUMERIC_WEIGHT(num) + 1) * DEC_DIGITS;
 	if (ddigits <= maxdigits && scale >= NUMERIC_DSCALE(num)
-		&& (NUMERIC_CAN_BE_SHORT(scale, NUMERIC_WEIGHT(num))
+		&& (NUMERIC_CAN_BE_SHORT(dscale, NUMERIC_WEIGHT(num))
 			|| !NUMERIC_IS_SHORT(num)))
 	{
 		new = duplicate_numeric(num);
 		if (NUMERIC_IS_SHORT(num))
 			new->choice.n_short.n_header =
 				(num->choice.n_short.n_header & ~NUMERIC_SHORT_DSCALE_MASK)
-				| (scale << NUMERIC_SHORT_DSCALE_SHIFT);
+				| (dscale << NUMERIC_SHORT_DSCALE_SHIFT);
 		else
 			new->choice.n_long.n_sign_dscale = NUMERIC_SIGN(new) |
-				((uint16) scale & NUMERIC_DSCALE_MASK);
+				((uint16) dscale & NUMERIC_DSCALE_MASK);
 		PG_RETURN_NUMERIC(new);
 	}
 
@@ -1206,12 +1254,12 @@ numerictypmodin(PG_FUNCTION_ARGS)
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("NUMERIC precision %d must be between 1 and %d",
 							tl[0], NUMERIC_MAX_PRECISION)));
-		if (tl[1] < 0 || tl[1] > tl[0])
+		if (tl[1] < NUMERIC_MIN_SCALE || tl[1] > NUMERIC_MAX_SCALE)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("NUMERIC scale %d must be between 0 and precision %d",
-							tl[1], tl[0])));
-		typmod = ((tl[0] << 16) | tl[1]) + VARHDRSZ;
+					 errmsg("NUMERIC scale %d must be between %d and %d",
+							tl[1], NUMERIC_MIN_SCALE, NUMERIC_MAX_SCALE)));
+		typmod = make_numeric_typmod(tl[0], tl[1]);
 	}
 	else if (n == 1)
 	{
@@ -1221,7 +1269,7 @@ numerictypmodin(PG_FUNCTION_ARGS)
 					 errmsg("NUMERIC precision %d must be between 1 and %d",
 							tl[0], NUMERIC_MAX_PRECISION)));
 		/* scale defaults to zero */
-		typmod = (tl[0] << 16) + VARHDRSZ;
+		typmod = make_numeric_typmod(tl[0], 0);
 	}
 	else
 	{
@@ -1242,8 +1290,8 @@ numerictypmodout(PG_FUNCTION_ARGS)
 
 	if (typmod >= 0)
 		snprintf(res, 64, "(%d,%d)",
-				 ((typmod - VARHDRSZ) >> 16) & 0xffff,
-				 (typmod - VARHDRSZ) & 0xffff);
+				 numeric_typmod_precision(typmod),
+				 numeric_typmod_scale(typmod));
 	else
 		*res = '\0';
 
@@ -7432,14 +7480,17 @@ apply_typmod(NumericVar *var, int32 typm
 	if (typmod < (int32) (VARHDRSZ))
 		return;
 
-	typmod -= VARHDRSZ;
-	precision = (typmod >> 16) & 0xffff;
-	scale = typmod & 0xffff;
+	precision = numeric_typmod_precision(typmod);
+	scale = numeric_typmod_scale(typmod);
 	maxdigits = precision - scale;
 
 	/* Round to target scale (and set var->dscale) */
 	round_var(var, scale);
 
+	/* but don't allow var->dscale to be negative */
+	if (var->dscale < 0)
+		var->dscale = 0;
+
 	/*
 	 * Check for overflow - note we can't do this before rounding, because
 	 * rounding could raise the weight.  Also note that the var's weight could
@@ -7517,9 +7568,8 @@ apply_typmod_special(Numeric num, int32
 	if (typmod < (int32) (VARHDRSZ))
 		return;
 
-	typmod -= VARHDRSZ;
-	precision = (typmod >> 16) & 0xffff;
-	scale = typmod & 0xffff;
+	precision = numeric_typmod_precision(typmod);
+	scale = numeric_typmod_scale(typmod);
 
 	ereport(ERROR,
 			(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
new file mode 100644
index dfc8688..91ac5ed
--- 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
  */
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
new file mode 100644
index 385e963..cc11995
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2119,6 +2119,69 @@ SELECT * FROM num_input_test;
 (13 rows)
 
 --
+-- 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
+               Table "public.num_typemod_test"
+   Column    |     Type      | Collation | Nullable | Default 
+-------------+---------------+-----------+----------+---------
+ millions    | numeric(3,-6) |           |          | 
+ thousands   | numeric(3,-3) |           |          | 
+ units       | numeric(3,0)  |           |          | 
+ thousandths | numeric(3,3)  |           |          | 
+ millionths  | numeric(3,6)  |           |          | 
+
+-- 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;
+ scale | millions  | thousands | units | thousandths | millionths 
+-------+-----------+-----------+-------+-------------+------------
+     0 |         0 |         0 |     0 |       0.000 |   0.000000
+     0 |   1000000 |      1000 |     1 |       0.001 |   0.000001
+     0 |   2000000 |      2000 |     2 |       0.002 |   0.000002
+     0 |   8000000 |      8000 |     8 |       0.008 |   0.000008
+     0 |  12000000 |     12000 |    12 |       0.012 |   0.000012
+     0 |  88000000 |     88000 |    88 |       0.088 |   0.000088
+     0 | 123000000 |    123000 |   123 |       0.123 |   0.000123
+     0 | 988000000 |    988000 |   988 |       0.988 |   0.000988
+       |       NaN |       NaN |   NaN |         NaN |        NaN
+(9 rows)
+
+-- invalid inputs
+INSERT INTO num_typemod_test (millions) VALUES ('inf');
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -6 cannot hold an infinite value.
+INSERT INTO num_typemod_test (millions) VALUES (999500000);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -6 must round to an absolute value less than 10^9.
+INSERT INTO num_typemod_test (thousands) VALUES (999500);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale -3 must round to an absolute value less than 10^6.
+INSERT INTO num_typemod_test (units) VALUES (999.5);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.
+INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 3 must round to an absolute value less than 1.
+INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 3, scale 6 must round to an absolute value less than 10^-3.
+--
 -- Test some corner cases for multiplication
 --
 select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
new file mode 100644
index a64f96e..982b6af
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -92,6 +92,7 @@ num_exp_sqrt|t
 num_exp_sub|t
 num_input_test|f
 num_result|f
+num_typemod_test|f
 nummultirange_test|t
 numrange_test|t
 onek|t
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
new file mode 100644
index 7e17c28..14b4acf
--- 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 ('
 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
 --
 

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 6abda2f1d2..d3c70667a3 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -545,8 +545,8 @@
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
 </programlisting>
-     The precision must be positive, the scale may be positive or negative
-     (see below).  Alternatively:
+     The precision must be positive, while the scale may be positive or
+     negative (see below).  Alternatively:
 <programlisting>
 NUMERIC(<replaceable>precision</replaceable>)
 </programlisting>
@@ -569,8 +569,8 @@ NUMERIC
     <note>
      <para>
       The maximum precision that can be explicitly specified in
-      a <type>NUMERIC</type> type declaration is 1000.  An
-      unconstrained <type>NUMERIC</type> column is subject to the limits
+      a <type>numeric</type> type declaration is 1000.  An
+      unconstrained <type>numeric</type> column is subject to the limits
       described in <xref linkend="datatype-numeric-table"/>.
      </para>
     </note>
@@ -578,38 +578,48 @@ NUMERIC
     <para>
      If the scale of a value to be stored is greater than the declared
      scale of the column, the system will round the value to the specified
-     number of fractional digits.  If the declared scale of the column is
-     negative, the value will be rounded to the left of the decimal point.
-     If, after rounding, the number of digits to the left of the decimal point
-     exceeds the declared precision minus the declared scale, an error is
-     raised.  Similarly, if the declared scale exceeds the declared precision
-     and the number of zero digits to the right of the decimal point is less
-     than the declared scale minus the declared precision, an error is raised.
+     number of fractional digits.  Then, if the number of digits to the
+     left of the decimal point exceeds the declared precision minus the
+     declared scale, an error is raised.
      For example, a column declared as
 <programlisting>
 NUMERIC(3, 1)
 </programlisting>
-     will round values to 1 decimal place and be able to store values between
-     -99.9 and 99.9, inclusive.  A column declared as
+     will round values to 1 decimal place and can store values between
+     -99.9 and 99.9, inclusive.
+    </para>
+
+    <para>
+     Beginning in <productname>PostgreSQL</productname> 15, it is allowed
+     to declare a <type>numeric</type> column with a negative scale.  Then
+     values will be rounded to the left of the decimal point.  The
+     precision still represents the maximum number of non-rounded digits.
+     Thus, a column declared as
 <programlisting>
 NUMERIC(2, -3)
 </programlisting>
-     will round values to the nearest thousand and be able to store values
-     between -99000 and 99000, inclusive.  A column declared as
+     will round values to the nearest thousand and can store values
+     between -99000 and 99000, inclusive.
+     It is also allowed to declare a scale larger than the declared
+     precision.  Such a column can only hold fractional values, and it
+     requires the number of zero digits just to the right of the decimal
+     point to be at least the declared scale minus the declared precision.
+     For example, a column declared as
 <programlisting>
 NUMERIC(3, 5)
 </programlisting>
-     will round values to 5 decimal places and be able to store values between
+     will round values to 5 decimal places and can store values between
      -0.00999 and 0.00999, inclusive.
     </para>
 
     <note>
      <para>
-      The scale in a <type>NUMERIC</type> type declaration may be any value in
-      the range -1000 to 1000.  (The <acronym>SQL</acronym> standard requires
-      the scale to be in the range 0 to <replaceable>precision</replaceable>.
-      Using values outside this range may not be portable to other database
-      systems.)
+      <productname>PostgreSQL</productname> permits the scale in
+      a <type>numeric</type> type declaration to be any value in the range
+      -1000 to 1000.  However, the <acronym>SQL</acronym> standard requires
+      the scale to be in the range 0
+      to <replaceable>precision</replaceable>.  Using scales outside that
+      range may not be portable to other database systems.
      </para>
     </note>
 
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 46cb37cea1..faff09f5d5 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -827,21 +827,31 @@ numeric_is_integral(Numeric num)
  *
  *	For purely historical reasons VARHDRSZ is then added to the result, thus
  *	the unused space in the upper 16 bits is not all as freely available as it
- *	might seem.
+ *	might seem.  (We can't let the result overflow to a negative int32, as
+ *	other parts of the system would interpret that as not-a-valid-typmod.)
  */
-static inline int
+static inline int32
 make_numeric_typmod(int precision, int scale)
 {
 	return ((precision << 16) | (scale & 0x7ff)) + VARHDRSZ;
 }
 
+/*
+ * Because of the offset, valid numeric typmods are at least VARHDRSZ
+ */
+static inline bool
+is_valid_numeric_typmod(int32 typmod)
+{
+	return typmod >= (int32) VARHDRSZ;
+}
+
 /*
  * numeric_typmod_precision() -
  *
  *	Extract the precision from a numeric typmod --- see make_numeric_typmod().
  */
 static inline int
-numeric_typmod_precision(int typmod)
+numeric_typmod_precision(int32 typmod)
 {
 	return ((typmod - VARHDRSZ) >> 16) & 0xffff;
 }
@@ -856,7 +866,7 @@ numeric_typmod_precision(int typmod)
  *	extends an 11-bit two's complement number x.
  */
 static inline int
-numeric_typmod_scale(int typmod)
+numeric_typmod_scale(int32 typmod)
 {
 	return (((typmod - VARHDRSZ) & 0x7ff) ^ 1024) - 1024;
 }
@@ -872,7 +882,7 @@ numeric_maximum_size(int32 typmod)
 	int			precision;
 	int			numeric_digits;
 
-	if (typmod < (int32) (VARHDRSZ))
+	if (!is_valid_numeric_typmod(typmod))
 		return -1;
 
 	/* precision (ie, max # of digits) is in upper bits of typmod */
@@ -1136,14 +1146,14 @@ numeric_support(PG_FUNCTION_ARGS)
 			int32		new_precision = numeric_typmod_precision(new_typmod);
 
 			/*
-			 * If new_typmod < VARHDRSZ, the destination is unconstrained;
-			 * that's always OK.  If old_typmod >= VARHDRSZ, the source is
+			 * If new_typmod is invalid, the destination is unconstrained;
+			 * that's always OK.  If old_typmod is valid, the source is
 			 * constrained, and we're OK if the scale is unchanged and the
 			 * precision is not decreasing.  See further notes in function
 			 * header comment.
 			 */
-			if (new_typmod < (int32) VARHDRSZ ||
-				(old_typmod >= (int32) VARHDRSZ &&
+			if (!is_valid_numeric_typmod(new_typmod) ||
+				(is_valid_numeric_typmod(old_typmod) &&
 				 new_scale == old_scale && new_precision >= old_precision))
 				ret = relabel_to_typmod(source, new_typmod);
 		}
@@ -1186,7 +1196,7 @@ numeric		(PG_FUNCTION_ARGS)
 	 * If the value isn't a valid type modifier, simply return a copy of the
 	 * input value
 	 */
-	if (typmod < (int32) (VARHDRSZ))
+	if (!is_valid_numeric_typmod(typmod))
 		PG_RETURN_NUMERIC(duplicate_numeric(num));
 
 	/*
@@ -1288,7 +1298,7 @@ numerictypmodout(PG_FUNCTION_ARGS)
 	int32		typmod = PG_GETARG_INT32(0);
 	char	   *res = (char *) palloc(64);
 
-	if (typmod >= 0)
+	if (is_valid_numeric_typmod(typmod))
 		snprintf(res, 64, "(%d,%d)",
 				 numeric_typmod_precision(typmod),
 				 numeric_typmod_scale(typmod));
@@ -7476,8 +7486,8 @@ apply_typmod(NumericVar *var, int32 typmod)
 	int			ddigits;
 	int			i;
 
-	/* Do nothing if we have a default typmod (-1) */
-	if (typmod < (int32) (VARHDRSZ))
+	/* Do nothing if we have an invalid typmod */
+	if (!is_valid_numeric_typmod(typmod))
 		return;
 
 	precision = numeric_typmod_precision(typmod);
@@ -7565,7 +7575,7 @@ apply_typmod_special(Numeric num, int32 typmod)
 		return;
 
 	/* Do nothing if we have a default typmod (-1) */
-	if (typmod < (int32) (VARHDRSZ))
+	if (!is_valid_numeric_typmod(typmod))
 		return;
 
 	precision = numeric_typmod_precision(typmod);