Question 89413 in PostgreSQL Question posted in
The official documentation can be found here.

Ordering is not working for string numerical values – Postgresql

I have a text field(Django) for the amount in the DB like

amount = models.TextField(null=True, blank=True)

SQL query

select id, amount from payment order by amount asc;

  id   | amount  |
-------+---------+
 12359 | 1111111 |  ----> entered as "1111111" i.e a string value             
 12360 | 122222  |               
 12342 | 20      |               
 12361 | 222222  |               
 12364 | 2222222 |               
 12362 | 2222222 |               
 12290 | 260.00  |               
 12291 | 260.00  |               
 12292 | 260.00  |               
 12336 | 32      |               
 12363 | 3333333 |               
 12337 | 355     |                
 12331 | 45      |               
 12341 | 740     |             
 12343 | 741     |

Problem – Ordering is not working as expected.

Is it the expected behavior?

Tags:

4

Answers


  1. 0

    When you use string, the ordering is based on ASCII based system.
    To mitigate this type of issues, it is better to use models.DecimalField.

    More on that here: https://docs.djangoproject.com/en/4.1/ref/models/fields/#decimalfield

    Login or Signup to reply.
  2. 0

    amount field should be numeric field not text field. Then Ordering will work.

    from decimal import Decimal

class Payment(models.Model):
    amount = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)

    Please run migration after that.

    Login or Signup to reply.
  3. 0

    Try one of these:

    select * from tab_name order by column_name::integer;

     select * from tab_name order by cast(column_name AS integer);

    select * from tab_name order by int8 (column_name);

    I hope help you.
    But I don’t get how why you keep yout integer in text or varchar. Later this make lot of issues for you, by space and speed.

    Login or Signup to reply.
  4. 0

    You can Cast the value as Float field and order by it:

    MyModel.objects.annotate(as_float=Cast('amount', FloatField())).order_by('as_float')

    But, as @IqbalHussain mentioned in his answer, it is better to change the model.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search