MIME-Version: 1.0
From: =?UTF-8?Q?Marcin_Barczy=C5=84ski?= <mbarczynski@starfishstorage.com>
Date: Wed, 8 May 2024 15:41:55 +0200
Message-ID: <CAOhG4wca6ZNZ3o+U7RRxHcWqF5jCZ1bhZSqLN1fNqxgpcboSDg@mail.gmail.com>
Subject: How to change NUMERIC type under a domain without rewriting a table?
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e7741a0617f17758"
Archived-At: <https://www.postgresql.org/message-id/CAOhG4wca6ZNZ3o%2BU7RRxHcWqF5jCZ1bhZSqLN1fNqxgpcboSDg%40mail.gmail.com>
Precedence: bulk

--000000000000e7741a0617f17758
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello!

In the following setup:

DROP DOMAIN IF EXISTS uint64 CASCADE;
DROP TABLE IF EXISTS demo;

CREATE DOMAIN uint64 AS NUMERIC(20, 0);
CREATE TABLE demo(key uint64);
INSERT INTO demo SELECT g FROM generate_series(1, 10000000) g;

I would like to change the type of "key" column to NUMERIC(40, 0).
When I run,

ALTER TABLE demo ALTER COLUMN key TYPE NUMERIC(40, 0);

the whole table gets rewritten. Due to the table size it's not an option in
my case.
But, if there was no domain in the middle, and the column type was
NUMERIC(20, 0), the command above would complete in-place without rewriting
any rows.
I attempted to modify the definition uint64 in pg_type table:

UPDATE pg_type SET typtypmod =3D 2621444 WHERE typname =3D 'uint64';

It seems to work. Is it safe? Does it have any unintended consequences?
Or maybe there is another way to achieve this?

--
Marcin Barczy=C5=84ski

--000000000000e7741a0617f17758
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello!<br><br>In the following setup:<br><br>DROP DOMAIN I=
F EXISTS uint64 CASCADE;<br>DROP TABLE IF EXISTS demo;<br><br>CREATE DOMAIN=
 uint64 AS NUMERIC(20, 0);<br>CREATE TABLE demo(key uint64);<br>INSERT INTO=
 demo SELECT g FROM generate_series(1, 10000000) g;<br><br>I would like to =
change the type of &quot;key&quot; column to NUMERIC(40, 0).<br>When I run,=
<br><br>ALTER TABLE demo ALTER COLUMN key TYPE NUMERIC(40, 0);<br><br>the w=
hole table gets rewritten. Due to the table size it&#39;s not an option in =
my case.<br>But, if there was no domain in the middle, and the column type =
was NUMERIC(20, 0), the command above would complete in-place without rewri=
ting any rows.<br>I attempted to modify the definition uint64 in pg_type ta=
ble:<br><br>UPDATE pg_type SET typtypmod =3D 2621444 WHERE typname =3D &#39=
;uint64&#39;;<br><br>It seems to work. Is it safe? Does it have any uninten=
ded consequences?<br>Or maybe there is another way to achieve this?<br><br>=
--<br>Marcin Barczy=C5=84ski<br></div>

--000000000000e7741a0617f17758--