ruby-on-railsrubydatabaseruby-on-rails-3postgresql

Rails PostgreSQL numeric field overflow ERROR

I have a decimal field for price on my schema and every time that i try to insert the price the pg comes with this ERROR. anybody could give me any light? thank's

configuration

t.decimal  "price",  :precision => 2, :scale => 2

ERROR

PG::Error: ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 2 must round to an absolute value less than 1.
: INSERT INTO "items" ("category_id", "name", "price", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) RETURNING "id"
Solution

  • I quote the manual on Arbitrary Precision Numbers:

    The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point.

    You cannot assign numbers >= 1 to a column of the data type numeric(2,2). There is just no room for digits before the decimal point.

    0.999 and 0.995 are in violation of the type as well, as they round to 1 with a given scale of 2.