digoal
2021-07-27
PostgreSQL , numeric
numeric scale支持负数, 向小数点左边round.
Allow numeric scale to be negative or greater than precision.
author Dean Rasheed <dean.a.rasheed@gmail.com>
Mon, 26 Jul 2021 13:13:47 +0000 (14:13 +0100)
committer Dean Rasheed <dean.a.rasheed@gmail.com>
Mon, 26 Jul 2021 13:13:47 +0000 (14:13 +0100)
commit 085f931f52494e1f304e35571924efa6fcdc2b44
tree 33a044ee407525cc995951b91cf15c8c3a384962 tree
parent efe080220942fb8c2fdca66a3ab436159f7db86b commit | diff
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
+--
+-- 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)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
