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++;
}

"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. — user330315
– user330315, Commented Apr 2, 2014 at 13:11
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. — Craig Ringer
– Craig Ringer, Commented Apr 3, 2014 at 2:14

Tomas Greif · Accepted Answer · 2014-04-02 13:31:46Z

7

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.

answered Apr 2, 2014 at 13:31

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data is return with a random ordering from postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related