UPSERT statement in Postgres on a partitioned table

Question

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:

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);

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?

Your help is greatly appreciated!

You have an update function/trigger that has nothing to do with the upsert code you posted? — Mike Sherrill 'Cat Recall', Dec 20 '12 at 23:16
So going with my example above the update trigger would be on the mytable2 partitioned table. The trigger is on each of the partitions and fires before update. It deletes the row where OLD.pid=pid (as in example above). It then inserts using the NEW.* values. This function/trigger is necessary to manage the table, since it is a large table. The other process I want to do is a daily merge into the table, since it is an analytics database and I want to get the new daily rows. Let me know if you have any more questions. I appreciate your response. — user3412041, Dec 21 '12 at 02:12
Sorry, I don't see a trigger. I don't see a partitioned table. I would replace the IN by an appropiate EXISTS construct, though. — wildplasser, Dec 21 '12 at 02:33

score 1 · Answer 1 · answered Apr 19 '13 at 04:27

Your problem here is in the intersection of triggers which return null, and upserts expecting a returned result. In essence what you are doing is:

delete row
insert row
Abort update (this is what messes with your upsert)
check if the update completed and if not insert.

This is a case where you are going to have some issues because your triggers break the flow of the software.

So how to make this work.....

I don't see an easy way to make this work over a significantly large set. I do think you are going to have to move the update part into a function that you can call. The specifics of that function are going to depend on how many rows you are actually updating at once. Then you could do an INSERT ... SELECT similar to what you are doing but with a function call instead of a CTE.

UPSERT statement in Postgres on a partitioned table

1 Answers1