From: | Marcin Barczyński <mbarczynski(at)starfishstorage(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | How to change NUMERIC type under a domain without rewriting a table? |
Date: | 2024-05-08 13:41:55 |
Message-ID: | CAOhG4wca6ZNZ3o+U7RRxHcWqF5jCZ1bhZSqLN1fNqxgpcboSDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2024-05-08 14:55:18 | Re: Need help migrating MSSQL2008R2 tables into PGSQL & make PGSQL mimic MSSQL behaviour. |
Previous Message | Tomas Vondra | 2024-05-08 11:32:45 | Re: Unexpected data when subscribing to logical replication slot |