Postgresql offset return same data

Question

I have a two tables. I want to do pagination when I call the query result is always same. Where is mistake?

select t1.id, status, category, lat, lng
    from public.p t1
join public.l t2 on t1.id=t2.id and user_id=4 limit 10 OFFSET 10

I doubt that using limit without order by can give random results as mentioned in the PostgreSQL manual.. This is also been defined in the SQL standard SQL tables and resultsets are always **orderless** without order by cause — Raymond Nijland, Apr 19 '19 at 10:36
But if you want to the next page increase the OFFSET to match your page size — Raymond Nijland, Apr 19 '19 at 10:38
"Always same" as what? The previous call *without* `OFFSET`? Or the same call? — Erwin Brandstetter, Apr 19 '19 at 13:04

Erwin Brandstetter · Accepted Answer · 2019-04-19T13:09:02.673

Strictly speaking, without ORDER BY Postgres is free to return any rows at all, since the order is undefined and any result is correct. (But Postgres will still apply OFFSET, so that should even "work" without ORDER BY.)

Retry with a (deterministic) ORDER BY:

select t1.id, status, category, lat, lng
from public.p t1
join public.l t2 on t1.id=t2.id and user_id=4
ORDER BY ?something?                             -- !
limit 10
offset 10

Be that as it may, LIMIT / OFFSET is typically not the best tool for pagination. See:

Optimize query with OFFSET on large table

Postgresql offset return same data

1 Answers1