Re: select question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: g <brian(at)wuwei(dot)govshops(dot)com>
Cc: database(at)gurubase(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: select question
Date: 2000-07-31 14:55:21
Message-ID: 9503.965055321@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message ioseph 2000-07-31 16:58:30 How I can undelete recodes?
Previous Message g 2000-07-31 14:42:34 Re: select question