Select consecutive number of rows starting from some number

Question

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data.

As far as I can observe, inserted data to a table is stored in no order but is retrieved by identical SELECT statements in the same order.

I could get use of 100 random rows, but random rows are not useful because I always need to retrieve the same data from the database in order to compare results of the algorithm I'm developing.

I don't want to order data by any field because otherwise I'd be biasing the selected data.

The sql statement should be something like:

SELECT field1, field2 FROM table1 WHERE row_number > 70000 LIMIT 100;

or probably even better (as I think LIMIT doesn't prevent the database from outputting every record above row number 70000):

SELECT field1, field2 FROM table1 WHERE row_number > 70000 AND row_number <= 70100;

What SELECT statement should I use?

Answer 1

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.