Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w4fdS-002Qs6-0l for pgsql-admin@arkaria.postgresql.org; Mon, 23 Mar 2026 13:48:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w4fdQ-000IYP-27 for pgsql-admin@arkaria.postgresql.org; Mon, 23 Mar 2026 13:48:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w4fdQ-000IYH-0d for pgsql-admin@lists.postgresql.org; Mon, 23 Mar 2026 13:48:00 +0000 Received: from rs.plausibolo.de ([85.214.83.89] helo=plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w4fdO-00000000dDg-27b7 for pgsql-admin@lists.postgresql.org; Mon, 23 Mar 2026 13:47:59 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id A42173809C0; Mon, 23 Mar 2026 14:47:53 +0100 (CET) Received: from plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavis, port 10024) with ESMTP id ArTSx3qo582f; Mon, 23 Mar 2026 14:47:53 +0100 (CET) Received: from [192.168.222.128] (unknown [91.26.205.242]) by rs.plausibolo.de (Postfix) with ESMTPSA id 4B2E03801D0; Mon, 23 Mar 2026 14:47:53 +0100 (CET) Content-Type: multipart/alternative; boundary="------------w0KLdgD1U6kNyOdrm3QnlEhc" Message-ID: Date: Mon, 23 Mar 2026 14:47:52 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Technical validation on altering atttypmod for numeric column in PostgreSQL To: pgsql-admin@lists.postgresql.org References: Content-Language: en-GB, de-DE From: Holger Jakobs Autocrypt: addr=holger@jakobs.com; keydata= xsDiBEG8IA8RBAC6WqUzEPTjest7MvFca5WlI47EgtKzOy1D1X9gSEmPj0sQrilNGEitY/+Q kPnCvI3odz1XXf/MZQtXwlxJA4lPmx8/K7MqBj9vh0J5jRznpE7l+SBDmmDobIqBAgl6BZvH 1C7e2y72h5T1/plrXonLUGzthqBtsvWl0ogoyTJqiwCg6AOhYX+Xbcl8/AXy4F4qbUq4LXsD /j6yCKRkyleN688y9YpuS5J6/ZZ/OBCM74pa2iNge3GmXn1JeUowF693QFCGHkpswmDIoGhL 5iM6GHj9HFB6rTXW5H3sR1y6ta7vXwoNBGkgiM89B1a/KsLT5L6MZgZ5AV0tj6vxwAsuLixS SDiNVNAd8vp2FfqdLlOgThsgVIwKBACsCzZa1VxpdoNSYnkTOSZhEjZkbWApdneQ2bTxXJgW +fWgVeLiojCp6I1AmQqzwHz8UN1nrsQjH8Rnxt2J/C5H/Ek9jlOFakFQXlPMZw3tNeTA1P3B 3zPyq6hKFPZHFklUquE3gHVjnX9qxSW0xvFirf6xAMCMYWRnCQHRgiXoAc0hSG9sZ2VyIEph a29icyA8aG9sZ2VyQGpha29icy5jb20+wnsEExECADsCGwMGCwkIBwMCAxUCAwMWAgECHgEC F4AWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCaVdWsQUJMQE4HAAKCRCKV5yTsxlhureAAJ96 hJrXLiFGQJknuPn8vYEFTvQe4gCgjfc8YoZBMiEgbgibbp+9Ho93YZLOwE0EQbwgEBAEAKIa ShfwcoKhx0LBF0zNz2yIOzXDRg1rKFM+cr0iLfDPNLZH01LJaw9BXLHqGRV9KYroITlHN+4N TOtgSDJpX/a9PckP0aSj5G4WeN5C9WjyFzX1uYFKf3kBOSL6EXL/rwyqkyWwbtjIKW5FKleH arGWFuHYXNMhCaIRABHiQUITAAMFA/9nVjqfChfD/kmYsksVy9lmUV+fiD1OPIxLAfnT8Beo 4ClxzL0lLOhMVjzsi2YaSEPSqPXw8kFK3a6oBIauTb/nlcrn3pFaeh7Iv2bOPmvrCgOo81Fc hsrzl+gZUgxGtv8S5+BI4/kfpUfJu8E8PgKmkTDhDR9GwwJo5y+JuSMeisJmBBgRAgAmAhsM FiEEnLiQsViStaeQTnmxileck7MZYboFAmlXVsYFCTEBODYACgkQileck7MZYbomuQCgj9xe vI2QAGxQS+u27l+7zpHvUAEAoNL+6+cWX1zG3GfEk5j0nAmwPtyi In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------w0KLdgD1U6kNyOdrm3QnlEhc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------w0KLdgD1U6kNyOdrm3QnlEhc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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

--------------w0KLdgD1U6kNyOdrm3QnlEhc--