title: How in PostgreSQL 12 update on coflict data which can have one column null or not null avoiding that a new row is created
body: <p>I cannot understand how to update data on conflict when I have a situation having a column null or not null. This actually creates a new row instead of merging into one or I have the error that constraints do not match in the on-conflict options.</p>
  <p>The query has to update data when I have the column <code>site_id === NULL</code> or <code>NOT NULL</code>.</p>
  <p>I tried 2 queries which should be in one but having one I get multiple inserted rows instead of one merged.</p>
  <p>This is what I tried</p>
  <ol>
  <li>I added the unique indexes</li>
  </ol>
  <pre><code>ALTER TABLE candidates DROP CONSTRAINT candidates_day_study_id_site_id_status_unique;
  
  CREATE UNIQUE INDEX candidates_4col_uni_idx ON candidates (&quot;day&quot;, study_id, site_id, status)
  WHERE site_id IS NOT NULL OR site_id IS NULL;
  
  CREATE UNIQUE INDEX candidates_3col_uni_idx ON candidates (&quot;day&quot;, study_id, status)
  WHERE
      site_id IS NULL;
  </code></pre>
  <p>Then my queries</p>
  <pre><code>INSERT INTO candidates (&quot;day&quot;, study_id, site_id, status, total, CURRENT)
          VALUES('2020-01-01T00:00:00.000Z', 'ABC', NULL, 'INCOMPLETE', 4, 4) ON CONFLICT (&quot;day&quot;, study_id, site_id, status)
      WHERE
          site_id IS NOT NULL
          OR site_id IS NULL DO
          UPDATE
          SET
              total = EXCLUDED.total,
              CURRENT = EXCLUDED.current;
  
  INSERT INTO candidates (&quot;day&quot;, study_id, status, total, current)
          VALUES('2020-01-01T00:00:00.000Z', 'ABCDE', 'INCOMPLETE', 5, 5) ON CONFLICT (&quot;day&quot;, study_id, status)
          where site_id is null do 
          UPDATE
          SET
              total = EXCLUDED.total,
              current = EXCLUDED.current;
  </code></pre>
  <p>I spent hours finding a solution with my indexes but or I end with the error or I end with the multiple rows instead of been updated</p>