5

I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case?

SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1;
 count
--------
1160
(1 row)

I expected a result of 1 or 0, but it gives 1160. Why?

13

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'.

Basically:

  • Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00'
  • Postgres then count(*)s them
  • Postgres then LIMITs that count

I suspect you're wanting it to do this:

SELECT COUNT(*) FROM ( 
    SELECT * FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1
);

As this is basically an existence check, one could also do:

SELECT (EXISTS 
           (SELECT 1 FROM data WHERE datetime < '2015-09-23 00:00:00')
       )::integer;
3

From the postgres manual (http://www.postgresql.org/docs/current/static/queries-limit.html):

If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows).

Limit does not constrain how many rows your query will scan - it only affects how many rows will show up in your record set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.