title: Idempotent INSERT not working as expected. Is my query logic faulty?
body: <p>I am expecting this to not insert data every time I execute this statement. It should only insert data effectively once a day. Thats what I want this to do. Is there something wrong with my logic here? I am using <code>date(now())</code> so that it inserts todays date. I need this to be an idempotent insert. </p>
  
  <pre><code>INSERT INTO newsletter_subscription_counts
            (query_date, community_id, mailing_list, subscription_count)
    SELECT date(now()), a.community_id, 
       a.newsletter_type::mail_list_types, 
       count(a.subscriber_user_id)
     FROM
        newsletter_subscribers_main a
     LEFT OUTER JOIN newsletter_subscription_counts b
     ON (a.community_id = b.community_id)
     AND (a.newsletter_type::mail_list_types = b.mailing_list)
     AND (a.created_at = b.query_date)
     WHERE b.query_date is null
     AND   b.mailing_list is null
  GROUP BY a.community_id, a.newsletter_type, a.created_at
  </code></pre>