title: One-statement Insert+delete in PostgreSQL
body: <p>Suppose I have a PostgreSQL table <code>t</code> that looks like</p>
  
  <pre><code> id | name | y
  ----+------+---
   0  | 'a'  | 0
   1  | 'b'  | 0
   2  | 'c'  | 0
   3  | 'd'  | 1
   4  | 'e'  | 2
   5  | 'f'  | 2
  </code></pre>
  
  <p>With id being the primary key and with a UNIQUE constraint on <code>(name, y)</code>.</p>
  
  <p>Suppose I want to update this table in such a way that the part of the data set with <code>y = 0</code> becomes (without knowing what is already there)</p>
  
  <pre><code> id | name | y
  ----+------+---
   0  | 'a'  | 0
   1  | 'x'  | 0
   2  | 'y'  | 0
  </code></pre>
  
  <p>I could use</p>
  
  <pre><code>DELETE FROM t WHERE y = 0 AND name NOT IN ('a', 'x', 'y');
  INSERT INTO t (name, y) VALUES ('a', 0), ('x', 0), ('y', 0)
      ON CONFLICT (name) DO NOTHING;
  </code></pre>
  
  <p>I feel like there must be a one-statement way to do this (like what upsert does for the task "update the existing entries and insert missing ones", but then for "insert the missing entries and delete the entries that should not be there"). Is there? I heard rumours that oracle has something called <code>MERGE</code>... I'm not sure what it does exactly.</p>