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

From: wangshuo(at)highgo(dot)com(dot)cn
To: Kevin Grittner <kgrittn(at)ymail(dot)com>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Re: [HACKERS] Is it necessary to rewrite table while increasing the scale of datatype numeric?
Date: 2013-09-16 09:28:43
Message-ID: ff308b974170fb49e796ecc6973559f0@highgo.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> "wangshuo(at)highgo(dot)com(dot)cn" <wangshuo(at)highgo(dot)com(dot)cn> wrote:
>
>> I modified the code for this situation.I consider it very simple.
>
>> It will does not modify the table file, when the scale has been
>> increased exclusively.
>

Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> This patch would allow data in a column which was not consistent
> with the column definition:
>
> test=# create table n (val numeric(5,2));
> CREATE TABLE
> test=# insert into n values ('123.45');
> INSERT 0 1
> test=# select * from n;
>   val  
> --------
>  123.45
> (1 row)
>
> test=# alter table n alter column val type numeric(5,4);
> ALTER TABLE
> test=# select * from n;
>   val  
> --------
>  123.45
> (1 row)
>
> Without your patch the ALTER TABLE command gets this error (as it
> should):
>
> test=# alter table n alter column val type numeric(5,4);
> ERROR:  numeric field overflow
> DETAIL:  A field with precision 5, scale 4 must round to an absolute
> value less than 10^1.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Thanks for your reply and test.
I had added a new function named ATNumericColumnChangeRequiresCheck to
check the data
when the scale of numeric increase.
Now,the ALTER TABLE command could prompt this error:

postgres=# alter table tt alter COLUMN t1 type numeric (5,4);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 4 must round to an absolute
value less than 10^1.
STATEMENT: alter table tt alter COLUMN t1 type numeric (5,4);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 4 must round to an absolute
value less than 10^1.

I packed a new patch about this modification.

I think this ' altering field type model ' could modify all the type
in database.
Make different modification to column‘s datatype for different
situation.
For example when you modify the scale of numeric, if you think that the
5.0 and 5.00 is different,
the table file must be rewritten; otherwise, needn't be rewritten.

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

Attachment Content-Type Size
numeric.patch text/x-diff 4.3 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2013-09-16 10:03:57 Re: Minmax indexes
Previous Message Marko Tiikkaja 2013-09-16 08:57:16 Re: plpgsql.print_strict_params