PostgreSQL generating missing records and group them with source table

I’m creating a PostgreSQL query and I’d like to have the missing records for each day filled in automatically.

It occurred to me that I could generate a table that contained zero values and then join a source table to it.

So I created this query, but the result still does not contain missing days, only existing records from the source database table. For example, records from “2021-08-01 00:00:00”, “2021-08-07 00:00:00” or “2021-08-08 00:00:00” are missing.

SELECT
  s."Date",
  s."PowerOn",
  s."Idle",
  s."Run",
  CONCAT_WS('%', ROUND(NULLIF(s."Run"::numeric, 0) / NULLIF(s."PowerOn"::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
    SELECT d."Date", bigint '0' AS "PowerOn", bigint '0' AS "Idle", bigint '0' AS "Run", text '0 %' AS "Effectivity" 
    FROM (
        SELECT generate_series(timestamp '2021-08-01 00:00:00'
                        , NOW()
                        , interval  '1 day')::timestamp
) d("Date")) f
JOIN "Absolute_OEE" s ON s."Machine" = 'Machine01'
WHERE
  s."Date" > '2021-08-01 00:00:00'
GROUP BY s."Date",s."PowerOn", s."Idle", s."Run"
ORDER BY s."Date"

Result:

Can you please advise me how I can group the records and add zero values for days that are not recorded?

Thanks for your advice and tips.

Answer

You can use LEFT JOIN and COALESCE

SELECT
  d."Date",
  coalesce(s."PowerOn", bigint '0') AS "PowerOn",
  coalesce(s."Idle", bigint '0') AS "Idle",
  coalesce(s."Run", bigint '0') AS "Run",
  CONCAT_WS('%', ROUND(NULLIF(coalesce(s."Run", bigint '0')::numeric, 0) / NULLIF(coalesce(s."PowerOn", bigint '0')::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
     SELECT generate_series(timestamp '2021-08-01 00:00:00'
                     , NOW()
                     , interval  '1 day')::timestamp
     ) d
LEFT JOIN "Absolute_OEE" s ON d."Date"= s."Date" 
   AND s."Machine" = 'Machine01'
   AND s."Date" > '2021-08-01 00:00:00' 
GROUP BY  d."Date",
  coalesce(s."PowerOn", bigint '0'),
  coalesce(s."Idle", bigint '0'),
  coalesce(s."Run", bigint '0')
ORDER BY d."Date"