title: PostgreSQL generating missing records and group them with source table
body: <p>I'm creating a PostgreSQL query and I'd like to have the missing records for each day filled in automatically.</p>
  <p>It occurred to me that I could generate a table that contained zero values and then join a source table to it.</p>
  <p>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 &quot;2021-08-01 00:00:00&quot;, &quot;2021-08-07 00:00:00&quot; or &quot;2021-08-08 00:00:00&quot; are missing.</p>
  <pre><code>SELECT
    s.&quot;Date&quot;,
    s.&quot;PowerOn&quot;,
    s.&quot;Idle&quot;,
    s.&quot;Run&quot;,
    CONCAT_WS('%', ROUND(NULLIF(s.&quot;Run&quot;::numeric, 0) / NULLIF(s.&quot;PowerOn&quot;::numeric, 0) * 100, 2), '') As &quot;Effectivity&quot;
  FROM (
      SELECT d.&quot;Date&quot;, bigint '0' AS &quot;PowerOn&quot;, bigint '0' AS &quot;Idle&quot;, bigint '0' AS &quot;Run&quot;, text '0 %' AS &quot;Effectivity&quot; 
      FROM (
          SELECT generate_series(timestamp '2021-08-01 00:00:00'
                          , NOW()
                          , interval  '1 day')::timestamp
  ) d(&quot;Date&quot;)) f
  JOIN &quot;Absolute_OEE&quot; s ON s.&quot;Machine&quot; = 'Machine01'
  WHERE
    s.&quot;Date&quot; &gt; '2021-08-01 00:00:00'
  GROUP BY s.&quot;Date&quot;,s.&quot;PowerOn&quot;, s.&quot;Idle&quot;, s.&quot;Run&quot;
  ORDER BY s.&quot;Date&quot;
  </code></pre>
  <p><strong>Result:</strong></p>
  <p><a href="https://i.stack.imgur.com/4QpSh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4QpSh.png" alt="enter image description here" /></a></p>
  <p>Can you please advise me how I can group the records and add zero values for days that are not recorded?</p>
  <p>Thanks for your advice and tips.</p>