Message-ID: <24bc88e97f83e7d0cca8b51c5689daccf01cefa1.camel@cybertec.at>
Subject: Re: Technical validation on altering atttypmod for numeric column
 in PostgreSQL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mauricio Martini <martini.mauricio@hotmail.com>,
 "pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Mon, 23 Mar 2026 14:55:40 +0100
In-Reply-To: 
 <BN7PR10MB2609BBFDA87D4BBC22ABF14D824BA@BN7PR10MB2609.namprd10.prod.outlook.com>
References: 
	<BN7PR10MB2609BBFDA87D4BBC22ABF14D824BA@BN7PR10MB2609.namprd10.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/24bc88e97f83e7d0cca8b51c5689daccf01cefa1.camel%40cybertec.at>
Precedence: bulk

On Mon, 2026-03-23 at 13:02 +0000, Mauricio Martini wrote:
> I am evaluating an approach to change the scale of a numeric=C2=A0column =
from numeric(18,2)
> to numeric(18,4)=C2=A0in a large table, aiming to avoid the cost of a ful=
l table rewrite.
>
> The proposed approach involves directly updating the PostgreSQL system ca=
talog:
>
> UPDATE pg_attribute SET atttypmod =3D (18 << 16 | 4) + 4 WHERE attrelid =
=3D 'table'::regclass
>   =C2=A0AND attname =C2=A0=3D 'column';
>
> Before considering this in practice, I would like to validate a few point=
s:
> =C2=A0* Is this approach considered safe from a data integrity perspectiv=
e?
> =C2=A0* Is there a risk of inconsistencies in the internal representation=
 of the numeric
>   =C2=A0type (especially regarding scale)?
> =C2=A0* Could this impact indexes, functions, or aggregation operations?
> =C2=A0* Is there any official recommendation or prior experience using th=
is approach in
>    production environments?
> =C2=A0* Are there additional risks related to rollback, maintenance, or f=
uture operations
>    (e.g., dump/restore, upgrades)?
>
> The goal is to determine whether this alternative is viable, or if we sho=
uld 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 an=
y
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