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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Why does increasing the precision of a numeric column rewrites the table?
Date: 2017-10-11 07:41:58
Message-ID: orkhvu$fkg$1@blaine.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Simon Riggs 2017-10-11 08:27:17 Re: startup process stuck in recovery
Previous Message Christophe Pettus 2017-10-11 07:09:23 Re: startup process stuck in recovery