Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit085f931

Browse files
committed
Allow numeric scale to be negative or greater than precision.
Formerly, when specifying NUMERIC(precision, scale), the scale had tobe in the range [0, precision], which was per SQL spec. This commitextends the range of allowed scales to [-1000, 1000], independent ofthe precision (whose valid range remains [1, 1000]).A negative scale implies rounding before the decimal point. Forexample, a column might be declared with a scale of -3 to round valuesto the nearest thousand. Note that the display scale remainsnon-negative, so in this case the display scale will be zero, and alldigits before the decimal point will be displayed.A scale greater than the precision supports fractional values withzeros immediately after the decimal point.Take the opportunity to tidy up the code that packs, unpacks andvalidates the contents of a typmod integer, encapsulating it in asmall set of new inline functions.Bump the catversion because the allowed contents of atttypmod havechanged for numeric columns. This isn't a change that requires are-initdb, but negative scale values in the typmod would confuse oldbackends.Dean Rasheed, with additional improvements by Tom Lane. Reviewed byTom Lane.Discussion:https://postgr.es/m/CAEZATCWdNLgpKihmURF8nfofP0RFtAKJ7ktY6GcZOPnMfUoRqA@mail.gmail.com
1 parentefe0802 commit085f931

File tree

7 files changed

+251
-43
lines changed

7 files changed

+251
-43
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 44 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -545,8 +545,8 @@
545545
<programlisting>
546546
NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
547547
</programlisting>
548-
The precision must be positive, the scalezero or positive.
549-
Alternatively:
548+
The precision must be positive,whilethe scalemay be positive or
549+
negative (see below).Alternatively:
550550
<programlisting>
551551
NUMERIC(<replaceable>precision</replaceable>)
552552
</programlisting>
@@ -569,8 +569,8 @@ NUMERIC
569569
<note>
570570
<para>
571571
The maximum precision that can be explicitly specified in
572-
a <type>NUMERIC</type> type declaration is 1000. An
573-
unconstrained <type>NUMERIC</type> column is subject to the limits
572+
a <type>numeric</type> type declaration is 1000. An
573+
unconstrained <type>numeric</type> column is subject to the limits
574574
described in <xref linkend="datatype-numeric-table"/>.
575575
</para>
576576
</note>
@@ -581,8 +581,48 @@ NUMERIC
581581
number of fractional digits. Then, if the number of digits to the
582582
left of the decimal point exceeds the declared precision minus the
583583
declared scale, an error is raised.
584+
For example, a column declared as
585+
<programlisting>
586+
NUMERIC(3, 1)
587+
</programlisting>
588+
will round values to 1 decimal place and can store values between
589+
-99.9 and 99.9, inclusive.
590+
</para>
591+
592+
<para>
593+
Beginning in <productname>PostgreSQL</productname> 15, it is allowed
594+
to declare a <type>numeric</type> column with a negative scale. Then
595+
values will be rounded to the left of the decimal point. The
596+
precision still represents the maximum number of non-rounded digits.
597+
Thus, a column declared as
598+
<programlisting>
599+
NUMERIC(2, -3)
600+
</programlisting>
601+
will round values to the nearest thousand and can store values
602+
between -99000 and 99000, inclusive.
603+
It is also allowed to declare a scale larger than the declared
604+
precision. Such a column can only hold fractional values, and it
605+
requires the number of zero digits just to the right of the decimal
606+
point to be at least the declared scale minus the declared precision.
607+
For example, a column declared as
608+
<programlisting>
609+
NUMERIC(3, 5)
610+
</programlisting>
611+
will round values to 5 decimal places and can store values between
612+
-0.00999 and 0.00999, inclusive.
584613
</para>
585614

615+
<note>
616+
<para>
617+
<productname>PostgreSQL</productname> permits the scale in a
618+
<type>numeric</type> type declaration to be any value in the range
619+
-1000 to 1000. However, the <acronym>SQL</acronym> standard requires
620+
the scale to be in the range 0 to <replaceable>precision</replaceable>.
621+
Using scales outside that range may not be portable to other database
622+
systems.
623+
</para>
624+
</note>
625+
586626
<para>
587627
Numeric values are physically stored without any extra leading or
588628
trailing zeroes. Thus, the declared precision and scale of a column

‎src/backend/utils/adt/numeric.c

Lines changed: 95 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -815,6 +815,62 @@ numeric_is_integral(Numeric num)
815815
return (arg.ndigits==0||arg.ndigits <=arg.weight+1);
816816
}
817817

818+
/*
819+
* make_numeric_typmod() -
820+
*
821+
*Pack numeric precision and scale values into a typmod. The upper 16 bits
822+
*are used for the precision (though actually not all these bits are needed,
823+
*since the maximum allowed precision is 1000). The lower 16 bits are for
824+
*the scale, but since the scale is constrained to the range [-1000, 1000],
825+
*we use just the lower 11 of those 16 bits, and leave the remaining 5 bits
826+
*unset, for possible future use.
827+
*
828+
*For purely historical reasons VARHDRSZ is then added to the result, thus
829+
*the unused space in the upper 16 bits is not all as freely available as it
830+
*might seem. (We can't let the result overflow to a negative int32, as
831+
*other parts of the system would interpret that as not-a-valid-typmod.)
832+
*/
833+
staticinlineint32
834+
make_numeric_typmod(intprecision,intscale)
835+
{
836+
return ((precision <<16) | (scale&0x7ff))+VARHDRSZ;
837+
}
838+
839+
/*
840+
* Because of the offset, valid numeric typmods are at least VARHDRSZ
841+
*/
842+
staticinlinebool
843+
is_valid_numeric_typmod(int32typmod)
844+
{
845+
returntypmod >= (int32)VARHDRSZ;
846+
}
847+
848+
/*
849+
* numeric_typmod_precision() -
850+
*
851+
*Extract the precision from a numeric typmod --- see make_numeric_typmod().
852+
*/
853+
staticinlineint
854+
numeric_typmod_precision(int32typmod)
855+
{
856+
return ((typmod-VARHDRSZ) >>16)&0xffff;
857+
}
858+
859+
/*
860+
* numeric_typmod_scale() -
861+
*
862+
*Extract the scale from a numeric typmod --- see make_numeric_typmod().
863+
*
864+
*Note that the scale may be negative, so we must do sign extension when
865+
*unpacking it. We do this using the bit hack (x^1024)-1024, which sign
866+
*extends an 11-bit two's complement number x.
867+
*/
868+
staticinlineint
869+
numeric_typmod_scale(int32typmod)
870+
{
871+
return (((typmod-VARHDRSZ)&0x7ff) ^1024)-1024;
872+
}
873+
818874
/*
819875
* numeric_maximum_size() -
820876
*
@@ -826,11 +882,11 @@ numeric_maximum_size(int32 typmod)
826882
intprecision;
827883
intnumeric_digits;
828884

829-
if (typmod< (int32) (VARHDRSZ))
885+
if (!is_valid_numeric_typmod(typmod))
830886
return-1;
831887

832888
/* precision (ie, max # of digits) is in upper bits of typmod */
833-
precision=((typmod-VARHDRSZ) >>16)&0xffff;
889+
precision=numeric_typmod_precision(typmod);
834890

835891
/*
836892
* This formula computes the maximum number of NumericDigits we could need
@@ -1084,20 +1140,20 @@ numeric_support(PG_FUNCTION_ARGS)
10841140
Node*source= (Node*)linitial(expr->args);
10851141
int32old_typmod=exprTypmod(source);
10861142
int32new_typmod=DatumGetInt32(((Const*)typmod)->constvalue);
1087-
int32old_scale= (old_typmod-VARHDRSZ)&0xffff;
1088-
int32new_scale= (new_typmod-VARHDRSZ)&0xffff;
1089-
int32old_precision= (old_typmod-VARHDRSZ) >>16&0xffff;
1090-
int32new_precision= (new_typmod-VARHDRSZ) >>16&0xffff;
1143+
int32old_scale=numeric_typmod_scale(old_typmod);
1144+
int32new_scale=numeric_typmod_scale(new_typmod);
1145+
int32old_precision=numeric_typmod_precision(old_typmod);
1146+
int32new_precision=numeric_typmod_precision(new_typmod);
10911147

10921148
/*
1093-
* If new_typmod< VARHDRSZ, the destination is unconstrained;
1094-
* that's always OK. If old_typmod>= VARHDRSZ, the source is
1149+
* If new_typmodis invalid, the destination is unconstrained;
1150+
* that's always OK. If old_typmodis valid, the source is
10951151
* constrained, and we're OK if the scale is unchanged and the
10961152
* precision is not decreasing. See further notes in function
10971153
* header comment.
10981154
*/
1099-
if (new_typmod< (int32)VARHDRSZ||
1100-
(old_typmod >= (int32)VARHDRSZ&&
1155+
if (!is_valid_numeric_typmod(new_typmod)||
1156+
(is_valid_numeric_typmod(old_typmod)&&
11011157
new_scale==old_scale&&new_precision >=old_precision))
11021158
ret=relabel_to_typmod(source,new_typmod);
11031159
}
@@ -1119,11 +1175,11 @@ numeric(PG_FUNCTION_ARGS)
11191175
Numericnum=PG_GETARG_NUMERIC(0);
11201176
int32typmod=PG_GETARG_INT32(1);
11211177
Numericnew;
1122-
int32tmp_typmod;
11231178
intprecision;
11241179
intscale;
11251180
intddigits;
11261181
intmaxdigits;
1182+
intdscale;
11271183
NumericVarvar;
11281184

11291185
/*
@@ -1140,17 +1196,19 @@ numeric(PG_FUNCTION_ARGS)
11401196
* If the value isn't a valid type modifier, simply return a copy of the
11411197
* input value
11421198
*/
1143-
if (typmod< (int32) (VARHDRSZ))
1199+
if (!is_valid_numeric_typmod(typmod))
11441200
PG_RETURN_NUMERIC(duplicate_numeric(num));
11451201

11461202
/*
11471203
* Get the precision and scale out of the typmod value
11481204
*/
1149-
tmp_typmod=typmod-VARHDRSZ;
1150-
precision= (tmp_typmod >>16)&0xffff;
1151-
scale=tmp_typmod&0xffff;
1205+
precision=numeric_typmod_precision(typmod);
1206+
scale=numeric_typmod_scale(typmod);
11521207
maxdigits=precision-scale;
11531208

1209+
/* The target display scale is non-negative */
1210+
dscale=Max(scale,0);
1211+
11541212
/*
11551213
* If the number is certainly in bounds and due to the target scale no
11561214
* rounding could be necessary, just make a copy of the input and modify
@@ -1160,17 +1218,17 @@ numeric(PG_FUNCTION_ARGS)
11601218
*/
11611219
ddigits= (NUMERIC_WEIGHT(num)+1)*DEC_DIGITS;
11621220
if (ddigits <=maxdigits&&scale >=NUMERIC_DSCALE(num)
1163-
&& (NUMERIC_CAN_BE_SHORT(scale,NUMERIC_WEIGHT(num))
1221+
&& (NUMERIC_CAN_BE_SHORT(dscale,NUMERIC_WEIGHT(num))
11641222
|| !NUMERIC_IS_SHORT(num)))
11651223
{
11661224
new=duplicate_numeric(num);
11671225
if (NUMERIC_IS_SHORT(num))
11681226
new->choice.n_short.n_header=
11691227
(num->choice.n_short.n_header& ~NUMERIC_SHORT_DSCALE_MASK)
1170-
| (scale <<NUMERIC_SHORT_DSCALE_SHIFT);
1228+
| (dscale <<NUMERIC_SHORT_DSCALE_SHIFT);
11711229
else
11721230
new->choice.n_long.n_sign_dscale=NUMERIC_SIGN(new) |
1173-
((uint16)scale&NUMERIC_DSCALE_MASK);
1231+
((uint16)dscale&NUMERIC_DSCALE_MASK);
11741232
PG_RETURN_NUMERIC(new);
11751233
}
11761234

@@ -1206,12 +1264,12 @@ numerictypmodin(PG_FUNCTION_ARGS)
12061264
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
12071265
errmsg("NUMERIC precision %d must be between 1 and %d",
12081266
tl[0],NUMERIC_MAX_PRECISION)));
1209-
if (tl[1]<0||tl[1]>tl[0])
1267+
if (tl[1]<NUMERIC_MIN_SCALE||tl[1]>NUMERIC_MAX_SCALE)
12101268
ereport(ERROR,
12111269
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1212-
errmsg("NUMERIC scale %d must be between0 and precision %d",
1213-
tl[1],tl[0])));
1214-
typmod=((tl[0] <<16) |tl[1])+VARHDRSZ;
1270+
errmsg("NUMERIC scale %d must be between%d and %d",
1271+
tl[1],NUMERIC_MIN_SCALE,NUMERIC_MAX_SCALE)));
1272+
typmod=make_numeric_typmod(tl[0],tl[1]);
12151273
}
12161274
elseif (n==1)
12171275
{
@@ -1221,7 +1279,7 @@ numerictypmodin(PG_FUNCTION_ARGS)
12211279
errmsg("NUMERIC precision %d must be between 1 and %d",
12221280
tl[0],NUMERIC_MAX_PRECISION)));
12231281
/* scale defaults to zero */
1224-
typmod= (tl[0] <<16)+VARHDRSZ;
1282+
typmod=make_numeric_typmod(tl[0],0);
12251283
}
12261284
else
12271285
{
@@ -1240,10 +1298,10 @@ numerictypmodout(PG_FUNCTION_ARGS)
12401298
int32typmod=PG_GETARG_INT32(0);
12411299
char*res= (char*)palloc(64);
12421300

1243-
if (typmod >=0)
1301+
if (is_valid_numeric_typmod(typmod))
12441302
snprintf(res,64,"(%d,%d)",
1245-
((typmod-VARHDRSZ) >>16)&0xffff,
1246-
(typmod-VARHDRSZ)&0xffff);
1303+
numeric_typmod_precision(typmod),
1304+
numeric_typmod_scale(typmod));
12471305
else
12481306
*res='\0';
12491307

@@ -7428,18 +7486,21 @@ apply_typmod(NumericVar *var, int32 typmod)
74287486
intddigits;
74297487
inti;
74307488

7431-
/* Do nothing if we havea default typmod (-1) */
7432-
if (typmod< (int32) (VARHDRSZ))
7489+
/* Do nothing if we havean invalid typmod */
7490+
if (!is_valid_numeric_typmod(typmod))
74337491
return;
74347492

7435-
typmod-=VARHDRSZ;
7436-
precision= (typmod >>16)&0xffff;
7437-
scale=typmod&0xffff;
7493+
precision=numeric_typmod_precision(typmod);
7494+
scale=numeric_typmod_scale(typmod);
74387495
maxdigits=precision-scale;
74397496

74407497
/* Round to target scale (and set var->dscale) */
74417498
round_var(var,scale);
74427499

7500+
/* but don't allow var->dscale to be negative */
7501+
if (var->dscale<0)
7502+
var->dscale=0;
7503+
74437504
/*
74447505
* Check for overflow - note we can't do this before rounding, because
74457506
* rounding could raise the weight. Also note that the var's weight could
@@ -7514,12 +7575,11 @@ apply_typmod_special(Numeric num, int32 typmod)
75147575
return;
75157576

75167577
/* Do nothing if we have a default typmod (-1) */
7517-
if (typmod< (int32) (VARHDRSZ))
7578+
if (!is_valid_numeric_typmod(typmod))
75187579
return;
75197580

7520-
typmod-=VARHDRSZ;
7521-
precision= (typmod >>16)&0xffff;
7522-
scale=typmod&0xffff;
7581+
precision=numeric_typmod_precision(typmod);
7582+
scale=numeric_typmod_scale(typmod);
75237583

75247584
ereport(ERROR,
75257585
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202107181
56+
#defineCATALOG_VERSION_NO202107261
5757

5858
#endif

‎src/include/utils/numeric.h

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,12 +17,22 @@
1717
#include"fmgr.h"
1818

1919
/*
20-
* Limit on the precision (and hence scale) specifiable in a NUMERIC typmod.
21-
* Note that the implementation limit on the length of a numeric value is
22-
* much larger --- beware of what you use this for!
20+
* Limits on the precision and scale specifiable in a NUMERIC typmod. The
21+
* precision is strictly positive, but the scale may be positive or negative.
22+
* A negative scale implies rounding before the decimal point.
23+
*
24+
* Note that the minimum display scale defined below is zero --- we always
25+
* display all digits before the decimal point, even when the scale is
26+
* negative.
27+
*
28+
* Note that the implementation limits on the precision and display scale of a
29+
* numeric value are much larger --- beware of what you use these for!
2330
*/
2431
#defineNUMERIC_MAX_PRECISION1000
2532

33+
#defineNUMERIC_MIN_SCALE(-1000)
34+
#defineNUMERIC_MAX_SCALE1000
35+
2636
/*
2737
* Internal limits on the scales chosen for calculation results
2838
*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp