public inbox for [email protected]
help / color / mirror / Atom feedFrom: Marcin Barczyński <[email protected]>
To: pgsql-general <[email protected]>
Subject: How to change NUMERIC type under a domain without rewriting a table?
Date: Wed, 8 May 2024 15:41:55 +0200
Message-ID: <CAOhG4wca6ZNZ3o+U7RRxHcWqF5jCZ1bhZSqLN1fNqxgpcboSDg@mail.gmail.com> (raw)
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 = 2621444 WHERE typname = '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ński
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: How to change NUMERIC type under a domain without rewriting a table?
In-Reply-To: <CAOhG4wca6ZNZ3o+U7RRxHcWqF5jCZ1bhZSqLN1fNqxgpcboSDg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox