Thread: alter table rename missing numeric type

alter table rename missing numeric type

From
pgsql-bugs@postgresql.org
Date:
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

Re: alter table rename missing numeric type

From
Tom Lane
Date:
> 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

I can't duplicate either part of this bug report, using either 7.0.2
or current sources.  Something's pretty whacked out about your
installation I think... what version are you running, anyway?

BTW, you didn't actually show us the command you used to create n2.

            regards, tom lane