Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric？

On Thu, Sep 5, 2013 at 6:41 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> Then if the column was
> altered from NUMERIC with scale to be a plain NUMERIC, it would have
> to rewrite the table to enforce the row-wise scale to match the old
> column-wise scale. Where as now that alter doesn't need a re-write.
> I don't know if this would be an overall gain or not.
>

We've talked about cases like this in the past. It's mostly a SOP and I
think it may already be on the TODO.

The main difficulty is that Postgres is very extensible. So to implement
this you need to think bigger than NUMERIC. It should also be possible to
alter a column from varchar(5) to varchar(10) for example (but not the
other way around).

One way to do it would be to extend pg_type to have another column that
specifies a function. That function would take the old and new typmod
(which is what stores the 5 in varchar(5)) and tell the server whether it's
a safe change to make without rechecking.

Another way might be to overload the cast functions, though they currently
receive no information about the typmod. They might have the benefit of
being able to handle things like varchar(5) -> text though.

But it has to be that general. Any data type should be able to specify
whether an old and new typmod are compatible.

--
greg