Attached is a more complete patch, with updated docs and tests.
I chose to allow the scale to be in the range -1000 to 1000, which, to
some extent, is quite arbitrary. The upper limit of 1000 makes sense,
because nearly all numeric computations (other than multiply, add and
subtract) have that as their upper scale limit (that's the maximum
display scale). It also has to be at least 1000 for SQL compliance,
since the precision can be up to 1000.
The lower limit, on the other hand, really is quite arbitrary. -1000
is a nice round number, giving it a certain symmetry, and is almost
certainly sufficient for any realistic use case (-1000 means numbers
are rounded to the nearest multiple of 10^1000).
Also, keeping some spare bits in the typemod might come in handy one
day for something else (e.g., rounding mode choice).
Regards,
Dean
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
--
