Re: change type from NUMERIC(14,4) to NUMERIC(24,12)

Torsten Förtsch wrote:
> we have a large table and want to change the type of one column from NUMERIC(14,4) to NUMERIC(24,12).
> If the new type is just NUMERIC without any boundaries, the operation is fast. If (24,12) is
> specified, it takes ages.
>
> I think it takes so long because the database wants to check that all data in the table is compatible
> with the new type. But the old type has stricter boundaries both before and after the dot. So, it is
> compatible. It has to be.
>
> Is there a way to change the type skipping the additional check?
>
> This is 9.6.

If you don't mind doing something unsupported, you could just modify
the attribute metadata in the catalog:

test=# CREATE TABLE a(x numeric(14,4));
CREATE TABLE
test=# INSERT INTO a VALUES (1234567890.1234);
INSERT 0 1
test=# UPDATE pg_attribute
SET atttypmod = atttypmod + (24 - 14) * 65536 + (12 - 4)
WHERE attrelid = 'a'::regclass AND attname = 'x';
UPDATE 1
test=# \d a
Table "public.a"
Column | Type | Modifiers
--------+----------------+-----------
x | numeric(24,12) |

test=# SELECT * FROM a;
x
-----------------
1234567890.1234
(1 row)

Yours,
Laurenz Albe