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 1w4evk-002Pup-1H for pgsql-admin@arkaria.postgresql.org; Mon, 23 Mar 2026 13:02:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w4evh-0001IJ-2P for pgsql-admin@arkaria.postgresql.org; Mon, 23 Mar 2026 13:02:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w4evh-0001IB-13 for pgsql-admin@lists.postgresql.org; Mon, 23 Mar 2026 13:02:49 +0000 Received: from mail-westus3azolkn19012036.outbound.protection.outlook.com ([52.103.23.36] helo=PH8PR06CU001.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w4evf-00000000ge2-0gw9 for pgsql-admin@lists.postgresql.org; Mon, 23 Mar 2026 13:02:49 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=V9D1ZxJ5befVv+44A4F8xi/xN2qZx8c+A7RYv2GmNqqzIM1xuFw4Dw/mI55iTjpGKzuDYsyLBEdwVZdKurKmdxFOQNJTnUF4oxmHu7L/a062CWK+Ma71X5YiORJQ9icZG307Y4hUYLuP89+NVesKhO1P5My7bNyJi9m+YkpgepK6H7MDQHyMuovYINp1JZSQOg0Vu4IcrXeqIu7U7kbwDCmqz5imo6zYKudTEH074Q2glT2wX+SPAVL7XKkIbt61hg3M2lVqw6LIB35J67XK30MmhU2O9g6+J0+5p/IP0y27zF1/CD867iL4blxCMeyxrjxlinh9kMWxKJ0Z69SYbw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=3NfB0pIB5Tux3DNApfFW0rc4DvWUZPHkEhmRBPN5t9M=; b=PtZP7VO4h2IGiJtPyCrmzbhC1d9VwujboyzT7hNUfBpqvq8L9Rg8Th7/kcAy5taStrUHQqgK7535Ezalr+JxMaUGCi9nQTlHZXXIycbEKPLriXlsgcaIlk/GnCxZxfKM316ds+Zk3ABQDxjalylnc4n29t0QvsRGTnjM3zW8UhqaarQHk2Aim0e2adobjo+w+eEcD5NZwlekXkqgJE5ARF7bp0SCoPz5Hns3vkiaKsh/FzSyW5mDkZbgllEG+aaPR18ZLDtKqdcItGWoIBt93y/uK4kA3QupGg68hJFnET75C2JII5oNGzYuHbVOhMBgZz/cbE0RmqpUAbXT35a+lw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=3NfB0pIB5Tux3DNApfFW0rc4DvWUZPHkEhmRBPN5t9M=; b=PVi4+ANLwGgbfk9t47hqrwSoqffA3IeuBIWMeW4PNKghIWBOsh4jxJKWJh7O9VSsLEnZUkzVGazHGXtTcfuLVnzM5iglXkpQPsUJtET/LO/gPcI29UZHFf1tilYQX2/7rmuGEus9Y0bn0bkEKf/LjUesRgjza+QA3ypm7rI2EaeUVEyK/qeoZaN3uA0kKSwCAll9EEZRFL1vhVfW6uMMzVv0j9tNY+PmKSKzho2HupZy01VJBWffXzKBA7/3MSLpyCoRu5ySDGQeDiP8kMEYdzxq6KFAOfMy/vVzB2mi0AO6DdH6zDmWi9fARVUejKoJIWXB4XmNAkaapmnbmMT7fA== Received: from BN7PR10MB2609.namprd10.prod.outlook.com (2603:10b6:406:cd::13) by CO1PR10MB4562.namprd10.prod.outlook.com (2603:10b6:303:93::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9723.31; Mon, 23 Mar 2026 13:02:43 +0000 Received: from BN7PR10MB2609.namprd10.prod.outlook.com ([fe80::93a3:d568:1ac1:afc8]) by BN7PR10MB2609.namprd10.prod.outlook.com ([fe80::93a3:d568:1ac1:afc8%5]) with mapi id 15.20.9723.030; Mon, 23 Mar 2026 13:02:42 +0000 From: Mauricio Martini To: "pgsql-admin@lists.postgresql.org" Subject: Technical validation on altering atttypmod for numeric column in PostgreSQL Thread-Topic: Technical validation on altering atttypmod for numeric column in PostgreSQL Thread-Index: AQHcusTiG0ZWGex8iEuxSI7Nghwmfw== Date: Mon, 23 Mar 2026 13:02:42 +0000 Message-ID: Accept-Language: pt-BR, en-US Content-Language: pt-BR X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BN7PR10MB2609:EE_|CO1PR10MB4562:EE_ x-ms-office365-filtering-correlation-id: a8b0c8f6-5745-4b1c-5a84-08de88dc788a x-microsoft-antispam: BCL:0;ARA:14566002|15080799012|15030799006|31061999003|461199028|24071999003|37011999003|25031999004|39105399006|20031999003|55001999003|8062599012|8060799015|19110799012|440099028|3412199025|102099032|40105399003; x-microsoft-antispam-message-info: =?iso-8859-1?Q?h8z5JWV5bjAX7o99oBapsiKaFcWtND1vXmrEpxn/1Uz2HdHBCvMpRSL3jO?= =?iso-8859-1?Q?mouDnGzzNL54kQHcoBLWDIY6mb9xw5462tI1LFxt39x0QKgszDSFlK+A7V?= =?iso-8859-1?Q?5Ra/YzsAVAp386n5cGzaEONVEmtPnN7Bm3s1M6xADdAKlJppAYmDr8Jg6H?= =?iso-8859-1?Q?ZR2gxFD+KWeLUCPnzhCcjKCiCJ8CCUcxq+Ta0btafs3TMdeNeuoWC6F4Ke?= =?iso-8859-1?Q?OKhAVbKtcY7YleJJWrw2sUwZxPGeHDnlRqYno8XTMg7frenhu6mIXG/CF9?= =?iso-8859-1?Q?yEpzHwN8WiR8oqLZjiVXLxxMEKZ2iGGnCXOXaGPZ3Z5rnouDcFs3fZdikL?= =?iso-8859-1?Q?ReXgwJkwsjnjwTUhM9SmqdrhRXKnROCCznVD3qaP/aj6P2Dd9+owm8ygdW?= =?iso-8859-1?Q?e8ObdTVtTnJMb7Fg3SjMMY/JM0ufDPiTxhUlRFdw27uYrjcW0lOAd5yFqb?= =?iso-8859-1?Q?aPLggFZHfRyqE7EE5rPu6Pps50hdreYvZ+A8x6mKofGin3DoQtr3CxoA6Y?= =?iso-8859-1?Q?OyvQPNTK+XXaaDM9ni9+R0T6atd1jWF3CTIIvagJJxxyrsMTuDUy+T6lj2?= =?iso-8859-1?Q?meWEzlrMHKwuw3G6Z+mc9E/DgZwsup81MhLSxWSf7DRgdVu+XkdbWJaH0y?= =?iso-8859-1?Q?lInQ1pkPRUWmh9fiIGSpAMjcs4cqEJf/DJKQnDVwIIyn+qS1smZjqiBrvf?= =?iso-8859-1?Q?RdpregVna0r/pNFdELz3/YDMMLoelharz1TxgKlgOxey3gzAyzIWG8yfVs?= =?iso-8859-1?Q?Sv+HO0ZbRyya1vCgYBkDVrYvQ37jk2vQUjFz7GZljQYeC2qiPMsf6XwngL?= =?iso-8859-1?Q?rfGKl/11SVL2vkkE8YvzxuXiAw7su7b3SVsQQrLYBTMuPPmFZESduvTA1W?= =?iso-8859-1?Q?EU4WgK19+c7KQATrzqD41/hibCQ6llVhF8Q3+n3JGRneO0z0/TkW9ge60z?= =?iso-8859-1?Q?S1/qkPiIEakdg/Whqiy1q0i1ie4MuD3CWl1glBxkJDLznI/n+dOYzj8lqZ?= =?iso-8859-1?Q?LkIYYM9+y5PgDyI45pnJ8S86XWp0YYJbfJuysRsjTkkN8UIov8Mxx26Cx7?= =?iso-8859-1?Q?NHLidky0xnqDur09UdNt4FuXAOJcbCmXQ7DKy/BTtAjN1yJtDduWKtApF/?= =?iso-8859-1?Q?N3U8a/065jypaeT/PzRRCrqpLpPlE=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?Ahm0aX2BiPEp2NFmU2HspiPDksWc7jK5YP4aM+Zwmrsk1CZGUAhmEo7JKa?= =?iso-8859-1?Q?KeWRUHSZP7zTZyj3v8OuUt6UdEju+rfUt3BFbaClg8jdsA5mU9yGVYQ4XH?= =?iso-8859-1?Q?gmrFB4pwAOOKBIlwnSNmlI1ra9xjaPbC33KNOiMwkd+Y9wdN3NFTHQLTkE?= =?iso-8859-1?Q?Lt1CpxjziS1Ce6p5VVsUZj/5xlZdXoljGBclu/rDsvtDj2ALQSDNj01srC?= =?iso-8859-1?Q?dIHdoIdeSzz2886mXtp68JXkoR1yRqM7jRJOuFG3Dl0vbqtmgUJmegwW2X?= =?iso-8859-1?Q?fU153zm96SMZOtFc33fPvhVmxKykolA0p9Y4lMGcoonmQOtxxbIeW6Zq4E?= =?iso-8859-1?Q?lsOgV6GdU9xp8Wh7JTgKi7OOtg7JwYJ1L6dmlcxkK3GvpEO9gmuOkd8fI+?= =?iso-8859-1?Q?HShRn0AiCmfIU48UOgK+kSVg9kDp7RhvEKwC/UxnnOWVnh1CawALGzRxlj?= =?iso-8859-1?Q?V6JCq/J1+Ewpe60Q13j4nrEwzoDrUWZG1m+WuQ+JkC033UXHW+NQgjrxyo?= =?iso-8859-1?Q?VptIwSB8LvCS+UyQU30SulbbL/A+6Cx6cFy4IrsafUrQDDXxvhyEpHn/G/?= =?iso-8859-1?Q?Ubrm7IZanwgWCKpAgq+ZwzT0wSl7VRGqbLfPZm1B/aDuJiVXjHE+adkW8d?= =?iso-8859-1?Q?Vn7nVcecRNY2h9iGM0yz+6s/zoKkbs4Tn8bFCnf58P5Ph7dRm6BQyevryH?= =?iso-8859-1?Q?O42tw95Sw1Jhkyi9YV8FZQpcvjO5xhwDou29Bzkk/RoKtAgViZ+KTNq9T4?= =?iso-8859-1?Q?ldwEQCUdeSb1rBNe/e4luK07stsE9Mz4fRl8+b433oq3QO2aei4BhXPwhY?= =?iso-8859-1?Q?2W021+en2IhI8cIJ5YF9oCJwtfSdMZ6WQQyvqN/AO6PPqqUHPLwf5W3GE4?= =?iso-8859-1?Q?EG16cNGDfQeJyP/TNlAYnq0EYuR3l9P+EhkjKLSVfGnIQtbPSMgF1PA9ec?= =?iso-8859-1?Q?hhVj8a9W3JCsqR8JmnBaossZXUEjmxD2lHHGueCC8d5Sz/cYg6am2h8oKw?= =?iso-8859-1?Q?YsldYVNJ+VV/SeFEm+vj9rlXwMSSRxZfPDRkm19rqtTwacNy83P3DW/VG/?= =?iso-8859-1?Q?n/MEC5VAkQ05huSfKVB9DZhF/WxgWh5ZKMHwc1TWL1VW/KUV2qrNaGH7TM?= =?iso-8859-1?Q?F0l+bQhkBFCnZDniRq/uiFolbnKcfI6jjwOjmzqnmUjumfT7ySXXEZJ1y8?= =?iso-8859-1?Q?Eqb9FFkRfCwqL2eJx9lE7T2OJPxkEkK0rZdtl9vverqq9fH2astWQr3CWS?= =?iso-8859-1?Q?ygJB/cGMuF+tAwZ178GkZQQIEoht+mvigTEzY+7r1qMRht/Lza0xC3ynmz?= =?iso-8859-1?Q?kkMxxdy59Jq7hYI//23dXA2uF+mfSlhDmu6chkM/VcBGF6b1tODYKnYZCf?= =?iso-8859-1?Q?bCLv1QyalRZ2UGoFSFUDGsYpnTU/2iaOaLanxf1p0i0W7bj6FkGGog8/5b?= =?iso-8859-1?Q?CvwXpdiyQBmpIWTZ?= Content-Type: multipart/alternative; boundary="_000_BN7PR10MB2609BBFDA87D4BBC22ABF14D824BABN7PR10MB2609namp_" MIME-Version: 1.0 X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-664b6.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BN7PR10MB2609.namprd10.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: a8b0c8f6-5745-4b1c-5a84-08de88dc788a X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Mar 2026 13:02:42.9116 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO1PR10MB4562 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BN7PR10MB2609BBFDA87D4BBC22ABF14D824BABN7PR10MB2609namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi team, I am evaluating an approach to change the scale of a numeric column from nu= meric(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 cata= log: UPDATE pg_attribute SET atttypmod =3D (18 << 16 | 4) + 4 WHERE attrelid =3D 'table'::regclass AND attname =3D '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 a= pproach in production environments? * Are there additional risks related to rollback, maintenance, or futur= e operations (e.g., dump/restore, upgrades)? The goal is to determine whether this alternative is viable, or if we shoul= d stick with more standard approaches (e.g., shadow column, incremental mig= ration, etc.). If anyone has experience with a similar scenario, your insights would be ap= preciated. Thanks. Att. Mauricio Martini --_000_BN7PR10MB2609BBFDA87D4BBC22ABF14D824BABN7PR10MB2609namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi team,
I am evaluating an approach to change the scale of a numeric&n= bsp;column from numeric(18,2) to numeric(18,4) in a large table, aiming to avoi= d the cost of a full table rewrite.
The proposed approach involves directly updating the PostgreSQL system cata= log:
UPDATE pg_attribute=0A=
SET atttypmod =3D (18 << 16 | 4) + 4=0A=
WHERE attrelid =3D 'table'::regclass=0A=
  AND attname  =3D '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 approac= h in production environments?
  • Are there additional risks related to rollback, maintenance, or future oper= ations (e.g., dump/restore, upgrades)?
The goal is to determine whether this alternative is viable, or if we shoul= d stick with more standard approaches (e.g., shadow column, incremental mig= ration, etc.).
If anyone has experience with a similar scenario, your insights would be ap= preciated.
Thanks.


= Att. Mauricio Martini

--_000_BN7PR10MB2609BBFDA87D4BBC22ABF14D824BABN7PR10MB2609namp_--