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) |