public inbox for [email protected]
help / color / mirror / Atom feedTechnical validation on altering atttypmod for numeric column in PostgreSQL
5+ messages / 4 participants
[nested] [flat]
* Technical validation on altering atttypmod for numeric column in PostgreSQL
@ 2026-03-23 13:02 Mauricio Martini <[email protected]>
2026-03-23 13:47 ` Re: Technical validation on altering atttypmod for numeric column in PostgreSQL Holger Jakobs <[email protected]>
2026-03-23 13:55 ` Re: Technical validation on altering atttypmod for numeric column in PostgreSQL Laurenz Albe <[email protected]>
2026-03-23 13:57 ` Re: Technical validation on altering atttypmod for numeric column in PostgreSQL Tom Lane <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: Mauricio Martini @ 2026-03-23 13:02 UTC (permalink / raw)
To: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Technical validation on altering atttypmod for numeric column in PostgreSQL
2026-03-23 13:02 Technical validation on altering atttypmod for numeric column in PostgreSQL Mauricio Martini <[email protected]>
@ 2026-03-23 13:47 ` Holger Jakobs <[email protected]>
2026-03-23 13:57 ` Re: Technical validation on altering atttypmod for numeric column in PostgreSQL Laurenz Albe <[email protected]>
2 siblings, 1 reply; 5+ messages in thread
From: Holger Jakobs @ 2026-03-23 13:47 UTC (permalink / raw)
To: [email protected]
Am 23.03.26 um 14:02 schrieb Mauricio Martini:
> 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
--
Holger Jakobs, Bergisch Gladbach
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Technical validation on altering atttypmod for numeric column in PostgreSQL
2026-03-23 13:02 Technical validation on altering atttypmod for numeric column in PostgreSQL Mauricio Martini <[email protected]>
2026-03-23 13:47 ` Re: Technical validation on altering atttypmod for numeric column in PostgreSQL Holger Jakobs <[email protected]>
@ 2026-03-23 13:57 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2026-03-23 13:57 UTC (permalink / raw)
To: Holger Jakobs <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Technical validation on altering atttypmod for numeric column in PostgreSQL
2026-03-23 13:02 Technical validation on altering atttypmod for numeric column in PostgreSQL Mauricio Martini <[email protected]>
@ 2026-03-23 13:55 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2026-03-23 13:55 UTC (permalink / raw)
To: Mauricio Martini <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Technical validation on altering atttypmod for numeric column in PostgreSQL
2026-03-23 13:02 Technical validation on altering atttypmod for numeric column in PostgreSQL Mauricio Martini <[email protected]>
@ 2026-03-23 13:57 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2026-03-23 13:57 UTC (permalink / raw)
To: Mauricio Martini <[email protected]>; +Cc: [email protected] <[email protected]>
Mauricio Martini <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-03-23 13:57 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-23 13:02 Technical validation on altering atttypmod for numeric column in PostgreSQL Mauricio Martini <[email protected]>
2026-03-23 13:47 ` Holger Jakobs <[email protected]>
2026-03-23 13:57 ` Laurenz Albe <[email protected]>
2026-03-23 13:55 ` Laurenz Albe <[email protected]>
2026-03-23 13:57 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox