PostgreSQL 15 preview - NUMERIC(precision, scale) , scale支持 -1000 到 1000

作者

digoal

日期

2021-07-27

标签

PostgreSQL , numeric

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=085f931f52494e1f304e35571924efa6fcdc2b44

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)  

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.