Is it safe to resize a numeric column using in pg_attribute without
altering the table?
I have a large table (over 900 million rows) that, due to some poor
design choices years ago, has a column numeric(8,2) that needs to
modified to numeric(12,2). I would like to avoid running an ALTER
TABLE statement on the table because of the time involved and the
number of views I would have to drop and recreate. I'm running 8.3.
I know that for varchar, it's possible to change the size of the
column by updating atttypmod in pg_attribute for the column. What I'm
wondering is if I can do the same thing for a numeric column? My
understanding is that numeric is stored without any null padding so I
was thinking this might work. There are no indexes on the column in
question and the table is not partitioned.
I've done some testing with a test table to see what happens and
everything seems to work okay but I wanted to make sure I'm not
missing something fundamental.
This is a test table with a million rows:
\d tester
Table "public.tester"
Column | Type | Modifiers
--------+--------------+-----------
num | numeric(8,2) |
select min(num), max(num) from tester;
min | max
----------+-----------
-8000.00 | 999999.99
UPDATE pg_attribute SET atttypmod = 786438
WHERE attrelid = 'tester'::regclass
AND attname = 'num';
UPDATE 1
Time: 32.895 ms
\d tester
Table "public.tester"
Column | Type | Modifiers
--------+---------------+-----------
num | numeric(12,2) |
Selects, inserts, and updates on the table all seem to work fine. I
created some test views that rely on the test table and they seem to
work fine as well. Are there any caveats I'm missing? Is there
something about how numeric is stored on disk that I need be concerned
about when altering the column in this method? Is it safe to just
alter the metadata?
Thanks,
--
Lukas Eklund
lukas@eklund.io
