Vince Vielhaber (vev@hub.org) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
alter table rename missing numeric type
Long Description
I start with table n1 which contains only a numeric type 4,2. Inserting a value larger than 99.99 causes an overflow.
Thencreate a second table which contains only a numeric type 10,2. Values larger than 99.99 insert fine as they
should. Renaming table n1 to oldn1 then renaming n2 to n1, attempting to insert a value larger than 99.99 fails. PG is
missingthe fact that the numeric datatype for the now called table n1 is defined as 10,2.
Subsequently dropping table n1 drops table oldn1! I got bit by this one once on hub.org but didn't notice how I did
it,I thought I dropped the wrong table - now I see otherwise.
Sample Code
template1=> create table n1(a numeric(4,2));
CREATE
template1=> insert into n1 values(50.23);
INSERT 360681 1
template1=> insert into n1 values(51.23);
INSERT 360682 1
template1=> insert into n1 values(52.23);
INSERT 360683 1
template1=> insert into n1 values(53.23);
INSERT 360684 1
template1=> insert into n1 values(154.23);
ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 scale 2
template1=> insert into n2 values(154.23);
INSERT 360703 1
template1=> alter table n1 rename to oldn1;
RENAME
template1=> alter table n2 rename to n1;
RENAME
template1=> insert into n1 values(154.23);
ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 scale 2
template1=> \d
Database = template1
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| vev | n1 | table |
| vev | oldn1 | table |
+------------------+----------------------------------+----------+
template1=> drop table n1;
DROP
template1=> \d
Database = template1
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| vev | n1 | table |
+------------------+----------------------------------+----------+
No file was uploaded with this report
