CODEWITHSUNDEEP
postgresqlmergepartitioningupsert
user3412041
user3412041

Reputation: 31

UPSERT statement in Postgres on a partitioned table

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!

Upvotes: 0

Views: 1578

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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:

  1. delete row
  2. insert row
  3. Abort update (this is what messes with your upsert)
  4. 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.

Upvotes: 1

Related Questions