Data is return with a random ordering from postgresql

Question

Our Java program is fetching data randomly from postgreSQL. how can it be resolved? What should be done in order to get data in a sequential order?

We have a table named grocery in our postgresql, which has entries from 0 to 99. the java code is fetching this entire table but in a random order.

connection = DriverManager.getConnection(
 "jdbc:postgresql://localhost/test", "postgres",
 "manafara");
connection.setAutoCommit(false);
Statement st = connection.createStatement();
String sql = "Select * from grocery";
ResultSet rs = st.executeQuery(sql);
Statement st1 = connection.createStatement();
ResultSet rs1 = st1.executeQuery("Select COUNT(*) AS TOTAL from grocery");
int n = 0;
while (rs1.next()) {
 n = rs1.getInt("TOTAL");
}
System.out.println("Count: " + n);
int a = 0;
Double db[][] = new Double[n][2];
while (rs.next()) {
 db[a][0] = (double) rs.getInt(4);
 db[a][1] = rs.getDouble(6);
 a++;
}

• user330315: "but in a random order" - well there is no order by so the database is free to choose any order it likes. Rows in a relational database are NOT sorted.
• Dhvhshah: how to fetch sequentially??
• Craig Ringer: It is not a random order. It's an arbitrary order. The difference is important. PostgreSQL will return them in whatever order is quickest, unless you tell it otherwise. Sometimes that'll be in the order of some index. Sometimes that'll be semirandom due to a hash join. Sometimes it'll be in whatever order the rows happen to be on disk. But it isn't random - there's a reason for the order that's returned, and it'll often be similar or the same between runs. Just not any particular order.

Answer 1

Quoting documentation:

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

This effectively means that without ORDER BY there is no guarantee at all that rows will be fetched in the same order from one execution to another.

If you need to get rows in the same order over and over again, you will need to add ORDER BY clause to your query. In your case it could be something like:

select * from grocery order by id

Values of column id have to be unique in order to guarantee same order of rows when fetching data.