Обсуждение: Technical validation on altering atttypmod for numeric column in PostgreSQL

UPDATE pg_attribute
SET atttypmod = (18 << 16 | 4) + 4
WHERE attrelid = 'table'::regclass  AND attname  = 'column';

• Is this approach considered safe from a data integrity perspective?
• Is there a risk of inconsistencies in the internal representation of the numeric type (especially regarding scale)?
• Could this impact indexes, functions, or aggregation operations?
• Is there any official recommendation or prior experience using this approach in production environments?
• Are there additional risks related to rollback, maintenance, or future operations (e.g., dump/restore, upgrades)?

P {margin-top:0;margin-bottom:0;}

Hi team,

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.

The proposed approach involves directly updating the PostgreSQL system catalog:

UPDATE pg_attribute
SET atttypmod = (18 << 16 | 4) + 4
WHERE attrelid = 'table'::regclass  AND attname  = 'column';

Before considering this in practice, I would like to validate a few points:

• Is this approach considered safe from a data integrity perspective?
• Is there a risk of inconsistencies in the internal representation of the numeric type (especially regarding scale)?
• Could this impact indexes, functions, or aggregation operations?
• Is there any official recommendation or prior experience using this approach in production environments?
• Are there additional risks related to rollback, maintenance, or future operations (e.g., dump/restore, upgrades)?

The goal is to determine whether this alternative is viable, or if we should stick with more standard approaches (e.g., shadow column, incremental migration, etc.).

If anyone has experience with a similar scenario, your insights would be appreciated.

Thanks.

Att. Mauricio Martini

Hi Maurcicio,

this change can lead to problems, because numbers with more than 16 digits before the decimal point wouldn't fit into the new data type.

It's always safer to use regular SQL. I'm not sure whether a change from numeric(18,2) to numeric(20,4) would cause a table re-write, but it's rather likely,

In general it's easier to be generous when choosing a data type so that later changes can be avoided. This doesn't apply to varchar lengths, because their change never triggers a re-write.

Kind Regards,

Holger

On Mon, 2026-03-23 at 13:02 +0000, Mauricio Martini wrote:
> 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.
>
> The proposed approach involves directly updating the PostgreSQL system catalog:
>
> UPDATE pg_attribute SET atttypmod = (18 << 16 | 4) + 4 WHERE attrelid = 'table'::regclass
>    AND attname  = 'column';
>
> Before considering this in practice, I would like to validate a few points:
>  * Is this approach considered safe from a data integrity perspective?
>  * Is there a risk of inconsistencies in the internal representation of the numeric
>    type (especially regarding scale)?
>  * Could this impact indexes, functions, or aggregation operations?
>  * Is there any official recommendation or prior experience using this approach in
>    production environments?
>  * Are there additional risks related to rollback, maintenance, or future operations
>    (e.g., dump/restore, upgrades)?
>
> The goal is to determine whether this alternative is viable, or if we should stick with more
> standard approaches (e.g., shadow column, incremental migration, etc.).
> If anyone has experience with a similar scenario, your insights would be appreciated.

It should be safe to perform this catalog modification, but I won't take any
liability.  "If it breaks, you get to keep both pieces."

Two things come to my mind when considering such a dangerous activity:

- LOCK the table in ACCESS EXCLUSIVE node while you perform the operation

- make sure there is no view that depends on the column, or if there is,
  drop the view first and re-create it afterwards

I myself would be a tad afraid to perform something like that.  Test well.

Yours,
Laurenz Albe

On Mon, 2026-03-23 at 14:47 +0100, Holger Jakobs wrote:
> > UPDATE pg_attribute SET atttypmod = (18 << 16 | 4) + 4 WHERE attrelid = 'table'::regclass  AND attname  = 'column';
> >
> >   Is this approach considered safe from a data integrity
> >   perspective?
>
> this change can lead to problems, because numbers with more than 16 digits before the decimal
> point wouldn't fit into the new data type.

Aww, right.
Goes to show how dangerous such operations are.

Yours,
Laurenz Albe

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