title: UPSERT statement in Postgres on a partitioned table
body: <p>I have a partitioned table and an update function/trigger.  when a row is updated, it first deletes the row and then inserts it into the proper partition.  My question is that I am trying to do a statement similar to a MERGE in Oracle.  I found reference to a similar implementation in Postgres using an UPSERT such as the following:</p>
  
  <pre><code>WITH upsert as
  (
     update mytable2 m 
        set sales=m.sales+d.sales, 
            status=d.status 
     from mytable d 
     where m.pid=d.pid
     RETURNING m.*
  )
  insert into mytable2 
  select a.pid, a.sales, 'NEW' 
  from mytable a 
  where a.pid not in (select b.pid from upsert b);
  </code></pre>
  
  <p>However, the problem is the update is firing first - causing a delete and an insert on a particular row and then the insert is inserting it again.  This is because of my update function/trigger on the partition.  Is there any way to get this to work as it would in Oracle with a merge (i.e. if row found, update it, else insert it) without causing dupes or having it fail on a constraint violation?</p>
  
  <p>Your help is greatly appreciated!</p>