0

an wondering why my data though in serial order by id when i look at the records in the database but when rendered, they are not in order of id?

for example: database

id animal
1  dog
2  lion
3  tiger

html View on the browser

id  animal
2   lion
1   dog
3   tiger
  • 4
    Rows in a relational database are NOT "sorted". Unless you use an order by the database is free to return them in any order it likes. – a_horse_with_no_name Oct 9 '16 at 7:24
  • thanks guys, appreciate your responses though I have not got chance to try "order by" but I expect it will work. However, this problem just developed on this version of a legacy code and that legacy code does not have this problem – olucube.com Oct 11 '16 at 5:25
2

As @a_horse_with_no_name already said: the data may not be stored in the sequential order.

You have some choices:

  1. Use a ORDER BY id[1] clause and force the database to sort the data for you.
  2. Force a CLUSTER[2] on the table to rewrite the data and sort based on a index as you want. I don't recommend that as a solution for your question but it is a alternative. IMPORTANT: CLUSTER command only will physically sort the data on the disk, rewriting the data files based on the index. There is no guarantee of the data will be returned on the same order.

Hope it helps.

References:

  1. https://www.postgresql.org/docs/current/static/queries-order.html
  2. https://www.postgresql.org/docs/current/static/sql-cluster.html
  • 1
    CLUSTER does not guarantee that the rows will be returned in that order. – ypercubeᵀᴹ Oct 10 '16 at 15:12
  • 1
    1. is correct but 2. is wrong. As ypercube already stated: cluster will not guarantee any ordering. The only way to get a guaranteed ordering is order by. Nothing else will do that. – a_horse_with_no_name Oct 10 '16 at 15:14
  • 1
    I totally agree with you both. With the CLUSTER command just the data will the sorted. I will edit the post. – Sebastian Webber Oct 10 '16 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.