Обсуждение: pgsql: Allow numeric scale to be negative or greater than precision.
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 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/085f931f52494e1f304e35571924efa6fcdc2b44 Modified Files -------------- doc/src/sgml/datatype.sgml | 48 ++++++++++- src/backend/utils/adt/numeric.c | 130 +++++++++++++++++++++-------- src/include/catalog/catversion.h | 2 +- src/include/utils/numeric.h | 16 +++- src/test/regress/expected/numeric.out | 63 ++++++++++++++ src/test/regress/expected/sanity_check.out | 1 + src/test/regress/sql/numeric.sql | 34 ++++++++ 7 files changed, 251 insertions(+), 43 deletions(-)
This is for migration? Or does it mean very small values now occupy less space since we store fewer zeros? Thanks On Mon, 26 Jul 2021 at 14:17, Dean Rasheed <dean.a.rasheed@gmail.com> wrote: > > 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 > > Branch > ------ > master > > Details > ------- > https://git.postgresql.org/pg/commitdiff/085f931f52494e1f304e35571924efa6fcdc2b44 > > Modified Files > -------------- > doc/src/sgml/datatype.sgml | 48 ++++++++++- > src/backend/utils/adt/numeric.c | 130 +++++++++++++++++++++-------- > src/include/catalog/catversion.h | 2 +- > src/include/utils/numeric.h | 16 +++- > src/test/regress/expected/numeric.out | 63 ++++++++++++++ > src/test/regress/expected/sanity_check.out | 1 + > src/test/regress/sql/numeric.sql | 34 ++++++++ > 7 files changed, 251 insertions(+), 43 deletions(-) > -- Simon Riggs http://www.EnterpriseDB.com/
Simon Riggs <simon.riggs@enterprisedb.com> writes:
> This is for migration?
Yeah, compatibility with some other DBMSes that allow this.
> Or does it mean very small values now occupy less space since we store
> fewer zeros?
It won't affect space consumption at all, as NUMERIC has never stored
leading or trailing zeroes. (Modulo the fact that "zero" here means
a base-NDIGIT digit.)
regards, tom lane
On Tue, 27 Jul 2021 at 17:22, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Simon Riggs <simon.riggs@enterprisedb.com> writes: > > This is for migration? > > Yeah, compatibility with some other DBMSes that allow this. > > > Or does it mean very small values now occupy less space since we store > > fewer zeros? > > It won't affect space consumption at all, as NUMERIC has never stored > leading or trailing zeroes. (Modulo the fact that "zero" here means > a base-NDIGIT digit.) Thanks, just trying to understand what that was for. -- Simon Riggs http://www.EnterpriseDB.com/