Обсуждение: BUG #4973: number precision or scale lost when altering table column

Поиск
Список
Период
Сортировка

BUG #4973: number precision or scale lost when altering table column

От
"terry"
Дата:
The following bug has been logged online:

Bug reference:      4973
Logged by:          terry
Email address:      94487509@qq.com
PostgreSQL version: 8.3.3
Operating system:   linux
Description:        number precision or scale lost when altering table
column
Details:

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

TEST=# CREATE TABLE B (C NUMERIC(8,3));
CREATE TABLE
TEST=# INSERT INTO B VALUES (12345.678);
INSERT 0 1
TEST=# SELECT * FROM B;
     c
-----------
 12345.678
(1 row)

TEST=# ALTER TABLE B ALTER COLUMN C TYPE NUMERIC(4,0);
ERROR:  numeric field overflow
DETAIL:  A field with precision 4, scale 0 must round to an absolute value
less than 10^4.
TEST=# ALTER TABLE B ALTER COLUMN C TYPE NUMERIC(5,0);
ALTER TABLE

/* scale lost */
TEST=# SELECT * FROM B;
   c
-------
 12346
(1 row)

TEST=#


oracle:
SQL> CREATE TABLE Y (I NUMERIC(4,2));

Table created.

SQL> INSERT INTO Y VALUES (12.21);

1 row created.

SQL> SELECT * FROM Y;

         I
----------
     12.21

ORACLE can not modify, because precision or scale will be lost
SQL> ALTER TABLE Y MODIFY I NUMERIC(3,0);
ALTER TABLE Y MODIFY I NUMERIC(3,0)
                     *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or
scale


SQL> ALTER TABLE Y MODIFY I NUMERIC(5,3);

Table altered.

SQL> INSERT INTO Y VALUES (12.123);

1 row created.

SQL> SELECT * FROM Y;

         I
----------
     12.21
    12.123

Re: BUG #4973: number precision or scale lost when altering table column

От
Tom Lane
Дата:
"terry" <94487509@qq.com> writes:
> TEST=# CREATE TABLE B (C NUMERIC(8,3));
> CREATE TABLE
> TEST=# INSERT INTO B VALUES (12345.678);
> INSERT 0 1
> TEST=# SELECT * FROM B;
>      c
> -----------
>  12345.678
> (1 row)

> TEST=# ALTER TABLE B ALTER COLUMN C TYPE NUMERIC(4,0);
> ERROR:  numeric field overflow
> DETAIL:  A field with precision 4, scale 0 must round to an absolute value
> less than 10^4.
> TEST=# ALTER TABLE B ALTER COLUMN C TYPE NUMERIC(5,0);
> ALTER TABLE

> /* scale lost */
> TEST=# SELECT * FROM B;
>    c
> -------
>  12346
> (1 row)

This is exactly the intended behavior.  We're not really interested in
Oracle's inability to handle the case.

            regards, tom lane