How to read all rows from huge table?
java, jdbc, postgresql
Solution
Use a CURSOR in PostgreSQL or let the JDBC-driver handle this for you.
LIMIT and OFFSET will get slow when handling large datasets.
Problem
I have a problem with processing all rows from database (PostgreSQL). I get an error: `org.postgresql.util.PSQLException: Ran out of memory retrieving query results.` I think that I need to read all rows in small pieces, but it doesn't work - it reads only 100 rows (code below). How to do that? ``` int i = 0; Statement s = connection.createStatement(); s.setMaxRows(100); // bacause of: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. ResultSet rs = s.executeQuery("select * from " + tabName); for (;;) { while (rs.next()) { i++; // do something... } if ((s.getMoreResults() == false) && (s.getUpdateCount() == -1)) { break; } } ```