CODEWITHSUNDEEP
postgresql
Brandon
Brandon

Reputation: 401

PostgreSQL - Made update to table in a function I created and now the order of rows in the table has changed

Does anyone know why the order of the rows changed after I made an update to table? Is there any way to make the order go back or change to another order eg:order by alphabetical?

This is the update I performed:

update t set amount = amount + 1 where account = accountNumber

After this update when I go and see the table, the order has changed

Upvotes: 1

Views: 38

Answers (1)

Guillaume F.
Guillaume F.

Reputation: 6473

A table doesn't have a natural row order, some database systems will actually refuse your query if you don't add an ORDER BY clause at the end of your SELECT

Why did the order change?

Because the database engine fetches your rows in the physical order they come from the storage. Some engines, like SQL Server, can have a CLUSTERED INDEX which forces a physical order, but it is still never really guaranteed that you get your results in that precise order.

The clustered index exist mostly as an optimization. PostgreSQL has a similar CLUSTER function to change the physical order, but it's an heavy process which locks the table : http://www.postgresql.org/docs/9.1/static/sql-cluster.html

How to force an alphabetical order of the rows?

Add an ORDER BY clause in your query.

SELECT * FROM table ORDER BY column

Upvotes: 2

Related Questions