BUG #4973: number precision or scale lost when altering table column - Mailing list pgsql-bugs

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