Mauricio Martini <martini.mauricio@hotmail.com> writes:
> I am evaluating an approach to change the scale of a numeric column from numeric(18,2) to numeric(18,4) in a large
table,aiming to avoid the cost of a full table rewrite.
This cannot work, per the comments for numeric_support():
* Planner support function for the numeric() length coercion function.
*
* Flatten calls that solely represent increases in allowable precision.
* Scale changes mutate every datum, so they are unoptimizable. Some values,
* e.g. 1E-1001, can only fit into an unconstrained numeric, so a change from
* an unconstrained numeric to any constrained numeric is also unoptimizable.
If it were safe, then that infrastructure would already be recognizing
that the ALTER TABLE doesn't require a rewrite.
Now, the "mutate every datum" bit is just referring to the dscale
field, and maybe you're okay with the dscale not changing (so that
existing entries would continue to print with 2 not 4 decimal places,
with follow-on implications for the number of decimal places in
arithmetic results). But there's another problem: (18,2) accepts some
values that (18,4) will not, since you're taking two digits away from
the integer part to give them to the fraction part. It would have to
be (20,4) to be certain that you didn't create a column containing
values it should not.
If you want to pursue this, I strongly urge experimenting with a test
table in a scratch database.
regards, tom lane
