Re: Is it necessary to rewrite table while increasing thescale of datatype numeric？

于 2013-09-04 23:41, Jeff Janes 回复:
> On Tue, Sep 3, 2013 at 9:08 PM,  <wangshuo@highgo.com.cn> wrote:
>> Hi, Hackers!
>>
>> I find that it takes a long time when I increase the scale of a
>> numeric
>> datatype.
>> By checking the code, I found that's because it needs to rewrite
>> that
>> table's file.
>> After checking that table's data file, I found only parameter
>> n_header
>> changed.
>> And, I found the data in that numeric field never changed.
>> So I thank It's not necessary to rewrite the table's file in this
>> case.
>>
>> Anyone has more idea about this, please come to talk about this!
>

Jeff Janes <jeff.janes@gmail.com> wrote:
> This was fixed in version 9.2.  You must be using an older version.
>
> Cheers,
>
> Jeff

Thanks for your reply.

To declare a column of type numeric use the syntax:
NUMERIC(precision, scale).
What I said is this scale,not yours.


I made a test on PG9.2, as fellow:
postgres=# select version();
                                                    version
--------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.7.2 20121109 (Red Hat 4.7.2-8), 64-bit
(1 row)

postgres=# create table tt(t1 numeric(10,5));
CREATE TABLE
postgres=# insert into tt values (2.56);
INSERT 0 1
postgres=# select pg_relation_filepath('tt');
  pg_relation_filepath
----------------------
  base/12914/16384
(1 row)

postgres=# alter table tt alter COLUMN t1 type numeric(10,6);
ALTER TABLE
postgres=# select pg_relation_filepath('tt');
  pg_relation_filepath
----------------------
  base/12914/16387
(1 row)

So thers's no need to rewrite the table.

      Wang Shuo
      HighGo Software Co.,Ltd.
      September 5, 2013