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 < '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



• 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;

StackExchange Database Administrators Q#117284, answer score: 20