Re: COUNT & Pagination

Hi,

David Shadovitz wrote, On 1/11/2004 7:10 PM:

> I understand that COUNT queries are expensive.  So I'm looking for advice on
> displaying paginated query results.
>
> I display my query results like this:
>
>   Displaying 1 to 50 of 2905.
>   1-50 | 51-100 | 101-150 | etc.
>
> I do this by executing two queries.  One is of the form:
>
>   SELECT <select list> FROM <view/table list> WHERE <filter> LIMIT m OFFSET n
>
> The other is identical except that I replace the select list with COUNT(*).
yes, you need 2 query. Or select it from one:
select *, (select count(*) from table) as count from table...

pg will optimize this query, and do the count only once

>
> And an unrelated question:
> I'm running PG 7.2.2 and want to upgrade to 7.4.1.  I've never upgraded PG
> before and I'm nervous.  Can I simply run pg_dumpall, install 7.4.1, and then
> feed the dump into psql?  I'm planning to use pg_dumpall rather than pg_dump
> because I want to preserve the users I've defined.  My database is the only one
> on the system.

yes. But check tha faq and the manual for a better explain.

C.