PostgreSQL changing returned rows order

Question

I have a table named categories, which contains ID(long), Name(varchar(50)), parentID(long), and shownByDefault(boolean) columns.

This table contains 554 records. All the shownByDefaultValues are 'false'.
When I execute 'select id, name from categories', pg returns me all the categories, orderer by its id.
Then I update some of the rows of the table('update categories set shownByDefault where parentId = 1'), update OK.
Then, when I try to execute the first query, which returns all the categories, they are returner with a very weird order.
I do not have problem to add 'order by', but since I am using JPA to get this values, anyone knows what the problem is or if there is a way to fix this?

Answer 1

That's not a problem. The order of rows returned by a SQL SELECT is undefined unless it has an ORDER BY. The order you get them is usually influenced by the order they are stored in the table and/or the indices that are used by the statement.

So depending on that order without using ORDER BY is a very, very bad idea.

If you need them in some order, simply specify that.

It is important that a table is a set of rows and not a sequence of rows.

Answer 2

From the docs:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Answer 3

The rows are returned in whatever their physical order on disk is; you can reorder them physically using the CLUSTER SQL command, but due to the way Postgres works they'll become unordered as soon as you start modifying rows.

For what you're doing an ORDER BY is the right answer.