Re: Select count with offset returns nothing. - Mailing list pgsql-general

From Jerry Sievers
Subject Re: Select count with offset returns nothing.
Date
Msg-id 87ty9way90.fsf@comcast.net
Whole thread Raw
In response to Select count with offset returns nothing.  (Tim Uckun <timuckun@gmail.com>)
List pgsql-general
Tim Uckun <timuckun@gmail.com> writes:

> I am using a library which is emitting SQL like this  SELECT COUNT(*)
> FROM batches LIMIT 15 OFFSET 15 the library fails because on postgres
> this query returns nothing (not even zero as a result). Presumably it
> returns some valid value on mysql and other databases.
>
> Other than hacking the library is there anything I can do?

Using offset 15 there is totally bogus since the count aggregate is
going to return just 1 row.  Limit 15 is harmless but unnecessary and
misleading

The only way you're going to get the result is with offset 0 or offset
omitted.

Something like limit 15 offset 15 sounds like a paginator feature
trying to show page 2 of pages having 15 rows each.

> Oddly enough SELECT count(*) FROM batches LIMIT 15 returns the full
> count of the table. Presumably the count has to be done on a subquery

I do not find this odd :-)

HTH

> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

pgsql-general by date:

Previous
From: Michael Black
Date:
Subject: Re: Select count with offset returns nothing.
Next
From: Condor
Date:
Subject: Re: Postgresql problem with update double precision