When specifying NUMERIC(precision, scale) the scale is constrained to
the range [0, precision], which is per SQL spec. However, at least one
other major database vendor intentionally does not impose this
restriction, since allowing scales outside this range can be useful.
A negative scale implies rounding before the decimal point. For
example, a column declared as NUMERIC(3,-3) rounds values to the
nearest thousand, and can hold values up to 999000.
(Note that the display scale remains non-negative, so all digits
before the decimal point are displayed, and none of the internals of
numeric.c need to worry about negative dscale values. Only the scale
in the typemod is negative.)
A scale greater than the precision constrains the value to be less
than 0.1. For example, a column declared as NUMERIC(3,6) can hold
"micro" quantities up to 0.000999.
Attached is a WIP patch supporting this.
Regards,
Dean
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index de561cd..1777c41
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -545,7 +545,7 @@
<programlisting>
NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
</programlisting>
- The precision must be positive, the scale zero or positive.
+ The precision must be positive.
Alternatively:
<programlisting>
NUMERIC(<replaceable>precision</replaceable>)
@@ -578,9 +578,28 @@ 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. A negative scale may be specified, to round
+ values to the left of the decimal point. The maximum absolute value
+ allowed in the column is determined by the declared precision minus the
+ declared scale. For example, a column declared as
+ <literal>NUMERIC(3, 1)</literal> can hold values between -99.9 and 99.9,
+ inclusive. If the value to be stored exceeds these limits, an error is
+ raised.
+ </para>
+
+ <para>
+ If the declared scale of the column is negative, stored values will be
+ rounded to the left of the decimal point. For example, a column declared
+ as <literal>NUMERIC(2, -3)</literal> will round values to the nearest
+ thousand and can store values between -99000 and 99000, inclusive.
+ </para>
+
+ <para>
+ If the declared scale of the column is greater than or equal to the
+ declared precision, stored values must only contain fractional digits to
+ the right of the decimal point. For example, a column declared as
+ <literal>NUMERIC(3, 5)</literal> can hold values between -0.00999 and
+ 0.00999, inclusive.
</para>
<para>
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
*/
