Correct use of COALESCE in postgres / COALESCE not working correctly

Question

I am trying to return an hourly report on the number of searches performed. My results do not include the hours when there are zero searches, I thought I had the syntax correct for using COALESCE. Can anyone see what I am doing wrong? Thanks

SELECT CAST(startdatetime as Date),extract(hour from startdatetime) as hr, COALESCE(count(distinct id),0) as average_per_hour
FROM search WHERE CAST(startdatetime As Date) = '2014/07/05' 
GROUP BY CAST(startdatetime as Date),extract(hour from startdatetime) 
ORDER BY CAST(startdatetime as Date),extract(hour from startdatetime)

Answer 1

Some refinement, but basically the same as @a_horse_with_no_name's answer:

SELECT DATE '2014-07-05', hr, COUNT(DISTINCT id) AS average_per_hour
FROM generate_series(0, 23) hr
LEFT JOIN search ON EXTRACT(HOUR FROM startdatetime) = hr AND CAST(startdatetime AS DATE) = '2014-07-05' 
GROUP BY hr
ORDER BY hr

Using CAST(startdatetime AS DATE) in ORDER BY & GROUP BY is irrelevant, because you search only one day. If that is not the case in general, you will need to tweak generate_series() too.

Edit:

This works across multiple days:

SELECT CAST(hr AS DATE), EXTRACT(HOUR FROM hr), COUNT(DISTINCT id) AS average_per_hour
FROM generate_series('2014-07-05 00:00:00', '2014-07-06 23:00:00', INTERVAL '1' HOUR) hr
LEFT JOIN search ON date_trunc('hour', startdatetime) = hr
GROUP BY hr
ORDER BY hr