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 @@ NUMERIC(precision, scale) - 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: NUMERIC(precision) @@ -578,11 +578,41 @@ NUMERIC 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 + +NUMERIC(3, 1) + + will round values to 1 decimal place and be able to store values between + -99.9 and 99.9, inclusive. A column declared as + +NUMERIC(2, -3) + + will round values to the nearest thousand and be able to store values + between -99000 and 99000, inclusive. A column declared as + +NUMERIC(3, 5) + + will round values to 5 decimal places and be able to store values between + -0.00999 and 0.00999, inclusive. + + + The scale in a NUMERIC type declaration may be any value in + the range -1000 to 1000. (The SQL standard requires + the scale to be in the range 0 to precision. + Using values outside this range may not be portable to other database + systems.) + + + 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 --