Re: select question

g <brian(at)wuwei(dot)govshops(dot)com> writes:
> Use the limit clause.
> SELECT message_text FROM messages ORDER BY creation_date LIMIT $limit,
> $offset.

> LIMIT 10, 0 gets you the first batch.
> LIMIT 10, 10 gets you the second batch.
> LIMIT 10, 20 gets you the third, etc.

BTW, a little tip that a number of people have gotten burnt by not
knowing: when you do this you *must* use an ORDER BY clause that's
strong enough to order the result rows completely. Otherwise you
are asking for slices out of an undefined ordering of the rows.
You could get a different ordering on each request, leading to
inconsistent slices --- in other words, missing or repeated rows.

This does actually happen in Postgres 7.0, because the planner
optimizes queries with small limit+offset differently from those
without.

regards, tom lane