COUNT(*) gives more than 1 with LIMIT 1? 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 &lt; '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?
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 &lt; '2015-09-23 00:00:00'.

Basically:


Postgres reads all the rows FROM data WHERE datetime &lt; '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 &lt; '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 &lt; '2015-09-23 00:00:00')
       )::integer;