=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> we have a large table and want to change the type of one column
> from NUMERIC(14,4) to NUMERIC(24,12). If the new type is just NUMERIC
> without any boundaries, the operation is fast. If (24,12) is specified, it
> takes ages.
> I think it takes so long because the database wants to check that all data
> in the table is compatible with the new type. But the old type has stricter
> boundaries both before and after the dot. So, it is compatible. It has to
> be.
Nope, numeric_transform doesn't think that:
* Flatten calls to numeric's length coercion function that solely represent
* increases in allowable precision. Scale changes mutate every datum, so
* they are unoptimizable.
The point being that "12.0000" is not quite the same thing as
"12.000000000000" --- they may represent notionally equal values, but
they're stored with different dscale fields, and that in turn has
consequences for subsequent arithmetic operations.
If you're willing to live with different dscales in the column, I'd
suggest just going to unconstrained numeric.
regards, tom lane
