Why postgres returns unordered data in select query, after updation of row?

by

Put very simply the “default order” is whatever it happens to read from the disk. Updating a row will not change the row in place… Usually it marks the old row as deleted and writes a new one.

When postgres reads rows from pages of memory, it will (probably) read them in the order they are stored on the page. It will read pages in whatever order it thinks is quickest (that may or may not be how they appear on disk). It can change based on whether or not it decides to use an index. So it can suddenly change without your app asking for anything different.

If you don’t specify an order by it will not take any action to re-order them.

NEVER rely on the default order. It is undefined behaviour.

More Related Contents:

  1. SQL SELECT speed int vs varchar
  2. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  3. SQL column reference “id” is ambiguous
  4. Refactor a PL/pgSQL function to return the output of various SELECT queries
  5. Is SELECT or INSERT in a function prone to race conditions?
  6. Index for finding an element in a JSON array
  7. Query for array elements inside JSON type
  8. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  9. Is it possible to make a recursive SQL query?
  10. Way to try multiple SELECTs till a result is available?
  11. Column ‘mary’ does not exist
  12. Get top row(s) with highest value, with ties
  13. Double colon `::` notation in SQL
  14. Delete parent if it’s not referenced by any other child
  15. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  16. PostgreSQL wildcard LIKE for any of a list of words
  17. Query that ignore the spaces
  18. SQL update fields of one table from fields of another one
  19. Postgres – Function to return the intersection of 2 ARRAYs?
  20. How to store array or multiple values in one column
  21. How to return two columns with function
  22. COPY with dynamic file name
  23. Table or column name cannot start with numeric?
  24. How to select records without duplicate on just one field in SQL?
  25. Count cumulative total in Postgresql
  26. PostgreSQL column ‘foo’ does not exist
  27. Prepared Statement on Postgresql in Rails
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment