Re: [GENERAL] Why does increasing the precision of a numeric column rewrites the table?

2017-10-11 Thread Tom Lane 
Thomas Kellerer  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

[GENERAL] Why does increasing the precision of a numeric column rewrites the table?

2017-10-11 Thread Thomas Kellerer 
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