Wednesday, November 25, 2015

Postgresql upsert example with CTE (before upsert support from 9.5)


 with heartbeat_data (source, time) as (  
  values ('test', now())  
 ),  
 update_query as (  
  update dvs_system.heartbeats  
    set last_beat = heartbeat_data.time  
  from heartbeat_data  
  where source_key = heartbeat_data.source  
  returning true as updated  
 )   
 insert into dvs_system.heartbeats (source_key, last_beat)  
  select source, time  
  from heartbeat_data  
  where not exists (  
   select 1 from update_query where updated = TRUE  
  )  
 ;  


Reference: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291

No comments:

Post a Comment