The following bug has been logged online:
Bug reference: 4973
Logged by: terry
Email address: 94487...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
