X
    Categories: Database

Error processing: DBD::Pg::st execute failed: ERROR: numeric field overflow

Bumped into this message :

Jul  7 00:13:54 server1 /usr/local/play/bin/getintodb.pl[12345]: 1067-345246.xml: 
Error processing: DBD::Pg::st execute failed: 
ERROR:  numeric field overflow
DETAIL:  The absolute value is greater than or equal to 10^2 for field with precision 3, scale 1. 

Hehehe… this is from posgresql database..

let’s examine the SQL statement before that error :

Jul  7 00:13:54 server1 /usr/local/bin/getintodb.pl[12345]: 
Calculated max_relative_humidity as 100.0 
Fatal SQL Error for SQL: INSERT INTO forecast_daily_lt
 (data_timestamp,station_id,gust_direction,gust_speed,
gust_timestamp,hail_duration,max_air_temperature,
max_qfe,max_qff,max_qnh,max_rainfall_15m,max_rainfall_30m,
max_relative_humidity,mean_air_temperature,mean_relative_humidity,
mean_wind_dir,mean_wind_speed,min_air_temperature,
min_qfe,min_qff,min_qnh,min_relative_humidity,rainfall_duration,
total_hail,total_rainfall) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and 
VALUES: 2009-06-07 16:00:00 UTC,67,210,3.2,
2009-06-07 19:00:00+00,0,32.8,
1007.6,1012.0,1012.3,0,0,100.0,
26.1,82.5,200,0.5,23.1,1004.7,1009.2,
1009.4,63.2,0,0,0 

database structure.. turn out to be among others..

\d forecast_daily_lt
               Table "public.forecast_daily"
         Column         |           Type           | Modifiers 
------------------------+--------------------------+-----------
 data_timestamp         | timestamp with time zone | 
...
 min_relative_humidity  | numeric(3,1)             | 
 mean_relative_humidity | numeric(3,1)             | 
 max_relative_humidity  | numeric(3,1)             | 
 ...
 total_hail             | numeric(4,1)             | 
 hail_duration          | numeric(3,0)             | 
Indexes:
    "forecast_daily_lt_idx1" UNIQUE, btree (data_timestamp, station_id)

10^2 is equal to 100.

and

numeric(3,1) mean .. 3 total width and with 1 decimal point..

so ..

the maximum it can take is 99.9

hence ..

it can't accept 100.1 which is 4 digit with 1 decimal point already ..

Solution : shall changed field to numeric (4,1)..

to change..

just need to issue this command via the SQL prompt.

 ALTER TABLE forecast_daily_lt ALTER column max_relative_humidity TYPE numeric(4,1);

so that it changed to :

               Table "public.forecast_daily_lt"
         Column         |           Type           | Modifiers 
------------------------+--------------------------+-----------
 max_relative_humidity  | numeric(4,1)             | 
Namran Hussin: a soft spoken guy... with exceptional interest in computers and technology. I love to learn new thing and also love to break thing for the sake of learning.. but I do abide to the self-imposed limitation or certain thing such as social thing in life, thing can be done and thing that must be avoided at whatever cost such as drug,illegal tracking, smoke,illicit activity..etc.muahahaha let's share what we had in this short term of the life.! make it worth of the living.~
Leave a Comment