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  


No comments:

Post a Comment