CODEWITHSUNDEEP
pythondjangopostgresqldjango-modelspostgresql-9.3
Mahammad Adil Azeem
Mahammad Adil Azeem

Reputation: 9392

A field with precision 10, scale 2 must round to an absolute value less than 10^8

I have a django-field total_price in postgres database version 9.3.11.

Here is the code:

total_value = models.DecimalField(decimal_places=100, default=0, max_digits=300)

I want to convert it to proper 2 decimal place. So I wrote this:

total_value = models.DecimalField(decimal_places=2, default=0, max_digits=10)

My migration file

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.db import models, migrations

class Migration(migrations.Migration):

    dependencies = [
        ('my_table', '0040_my_table_skipped'),
    ]

    operations = [
        migrations.AlterField(
            model_name='my_table',
            name='total_value',
            field=models.DecimalField(default=0, 
                                      max_digits=10, 
                                      decimal_places=2),
        ),
    ]

When I run command python manage.py migrate

I get an error from postgresql:

A field with precision 10, scale 2 must round to an absolute value less than 10^8.

Upvotes: 25

Views: 82927

Answers (4)

Minimul
Minimul

Reputation: 4215

In short, keep going higher on the precision until it works e.g.

ALTER TABLE "my_table" ALTER COLUMN "column_name" TYPE decimal(12,2)
ALTER TABLE "my_table" ALTER COLUMN "column_name" TYPE decimal(14,2) 
ALTER TABLE "my_table" ALTER COLUMN "column_name" TYPE decimal(16,2)

etc.

Upvotes: 0

Eric Leschinski
Eric Leschinski

Reputation: 154083

Reproduce this postgresql error:

ERROR:  numeric field overflow 
A field with precision 5, scale 4 must round to an absolute 
value less than 10^1

Create a postgresql table with a column with type: numeric(4,4) then try to insert data from another table that has a numeric(5,4) column type:

CREATE TABLE mytable( mycolumn numeric(5,4)); 
insert into mytable values(12.3456); 
CREATE TABLE mytable2( mycolumn numeric(4,4)); 
insert into mytable2 ( select * from mytable ); --Error thrown here

Solution

Increase the space allotted to the numeric column receiving the data or define a custom cast function to make the number fit into its destination.

Upvotes: 1

bh4r4th
bh4r4th

Reputation: 4450

As @sivakumar-r mentioned above, it's the precision and scale issue.

Let's see how the Numeric(precision, scale) works:

Suppose, if we have a column defined with NUMERIC(10,3).

This means, we can only save 10 digits in total with 3 spaces after decimal point.

So the range you can save in this case is: +9999999.999 to -9999999.999.

One Solution Could be:

Alter table column to use type NUMERIC without the precision and scale.

This will accept up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. As mentioned in postgres:11 Numeric types document.

Upvotes: 37

Sivakumar R
Sivakumar R

Reputation: 51

Earlier you had decimal number like Number(300, 100). I think you have some data in this decimal field. Now you alter the decimal field to Number(10,2). So now you can only save max 99999999.99. But your old data is much more than this. Because earlier you can save decimal number of 300 max digits and 100 decimal places.

Upvotes: 3

Related Questions