Thread: [GENERAL] Why does increasing the precision of a numeric column rewrites thetable?
[GENERAL] Why does increasing the precision of a numeric column rewrites thetable?
From
Thomas Kellerer
Date:
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
Re: [GENERAL] Why does increasing the precision of a numeric column rewrites the table?
From
Tom Lane
Date:
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 changesmutate 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