preserve generate_series rows through joins I have a histogram query that filters the entities counted by conditions that can only be checked if joins are used.

The query looks something like this:

SELECT gs.t, COUNT(packets.id) FROM packets
RIGHT OUTER JOIN 
  (SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t) gs
ON packets.transceived_at &gt; gs.t AND packets.transceived_at &lt;= gs.t + '1 hour'
JOIN devices ON devices.id = packets.device_id
WHERE devices.mandate_id = 3
GROUP BY gs.t;


Sadly this will omit rows where there's no packet that was received between gs.t and gs.t + '1 hour'.

This does not happen when I don't join devices, but I need that table (and others in derivative queries) to filter some devices.

Is there a way to still get all rows of generate_series even when using joins?
If you want the COUNT of each element of the series you can use an scalar subquery.

WITH gs AS
(
    SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t
)
SELECT gs.t, (SELECT COUNT(packets.id) 
              FROM   packets 
              JOIN   devices 
              ON     devices.id = packets.device_id
              WHERE  devices.mandate_id = 3
              AND    packets.transceived_at &gt; gs.t 
              AND    packets.transceived_at &lt;= gs.t + '1 hour')
FROM   gs;