From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mauricio Martini <martini.mauricio@hotmail.com>
cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: Technical validation on altering atttypmod for numeric column in
 PostgreSQL
In-reply-to: 
 <BN7PR10MB2609BBFDA87D4BBC22ABF14D824BA@BN7PR10MB2609.namprd10.prod.outlook.com>
References: 
 <BN7PR10MB2609BBFDA87D4BBC22ABF14D824BA@BN7PR10MB2609.namprd10.prod.outlook.com>
Comments: In-reply-to Mauricio Martini <martini.mauricio@hotmail.com>
	message dated "Mon, 23 Mar 2026 13:02:42 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3531044.1774274277.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 23 Mar 2026 09:57:57 -0400
Message-ID: <3531045.1774274277@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3531045.1774274277%40sss.pgh.pa.us>
Precedence: bulk

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 valu=
es,
 * e.g. 1E-1001, can only fit into an unconstrained numeric, so a change f=
rom
 * an unconstrained numeric to any constrained numeric is also unoptimizab=
le.

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