PostgreSQL – Why Does COUNT(*) Give More Than 1 with LIMIT 1?

countlimitspostgresql

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?

Best Answer

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;
Related Question