Why does increasing the precision of a numeric column rewrites the table?
When increasing the length constraint on a varchar column, Postgres is smart enough to not rewrite the table.
I expected the same thing to be true when increasing the size of a numeric column.
However this does not seem to be the case:
Consider the following table:
create table foo
(
some_number numeric(12,2)
);
The following statement returns "immediately", regardless of the number of rows in the table
alter table foo alter column some_number numeric(15,2);
However, when running (on the original table definition)
alter table foo alter column some_number numeric(15,3);
it takes quite a while (depending on the number of rows) which indicates a table rewrite is taking place.
I don't understand why going from numeric(12,2) to numeric(15,3) would require a table rewrite.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thomas Kellerer <spam_eater@gmx.net> writes:
I don't understand why going from numeric(12,2) to numeric(15,3) would require a table rewrite.
The comment for numeric_transform explains this:
* Flatten calls to numeric's length coercion function that solely represent
* increases in allowable precision. Scale changes mutate every datum, so
* they are unoptimizable. Some values, e.g. 1E-1001, can only fit into an
* unconstrained numeric, so a change from an unconstrained numeric to any
* constrained numeric is also unoptimizable.
The issue is basically that changing '1.00' to '1.000' requires a change
in the actually-stored value.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general