COUNT(*) gives more than 1 with LIMIT 1?

Question

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?

score 21 · Accepted Answer · edited Oct 14 '15 at 08:32

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;

score 3 · Answer 2 · edited Oct 07 '15 at 14:32

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.

score 0 · Answer 3 · answered Dec 13 '19 at 07:10

  SELECT 
    count(x.*) OVER() AS total_count,
    x.id,
    x.data_name
  FROM (
         SELECT 
           id,
           data_name
         FROM data 
         WHERE 
           datetime < '2015-09-23 00:00:00' 
         LIMIT 1
       ) x;

It Will give result as you want and you can add more fields to fetch and get aggregated records in json also

COUNT(*) gives more than 1 with LIMIT 1?

3 Answers3