Home Ask Login Register

Developers Planet

Your answer is one click away!

Jay Littles February 2016

Number order ignores decimals in Django Postgres

Building locally in SQLite. My app has numbers for prices of object. In SQLite I was able to sort prices regulary (ex: $914, $799, $120, $95, 9.00, 7.50.)

After pushing the app to Postgres the numbers come up as such: 95, 914, 9.00, 799, 7.50, 120.

I'm obviously looking to order them by price high to low and low to high in Postgres. What am I doing wrong?

Model Field:

price = models.DecimalField(max_digits=8, decimal_places=2, null=True)

URL:

url(r'^browse/price_desc/$', 'collection.views.price_desc', name="pricehigh")

View:

def price_desc(request):
       items = item.objects.all.order_by('-price')

       return render(request, 'index.html', {
            'items' : items,
})

Answers


knelson February 2016

First off, the decimalField is based on a python decimal.

According to this answer on a question about Decimal values, the way postgres store decimals is by a packed BCD string. The way your order is show reflects the order of sorted strings, not numbers.

Not too sure how django implements the sorting of DecimalField, but I am assuming it is sorting it as it would a string.

While the DecimalField seems appropriate for your use-case, you may want to switch to FloatField in order to get the sorting you are looking for.


Matthew Schinckel February 2016

Postgres stores and orders NUMERIC values as you would expect:

CREATE TABLE numeric_test (id SERIAL, value NUMERIC(8,2));
INSERT INTO numeric_test (value) VALUES (914), (799), (120), (95), (9), ('7.5');
SELECT * FROM numeric_test;
 id │ value  
────┼────────
  1 │ 914.00
  2 │ 799.00
  3 │ 120.00
  4 │  95.00
  5 │   9.00
  6 │   7.50
(6 rows)

I suspect then that your database is not storing as NUMERIC. Perhaps your migration is actually creating the column as TEXT?

Post Status

Asked in February 2016
Viewed 2,447 times
Voted 4
Answered 2 times

Search




Leave an answer


Quote of the day: live life