MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

  1.  
  2. MERGE INTO acme_obj_value d
  3. USING ( SELECT object_id
  4.         FROM   acme_state_tmp
  5.       ) s
  6. ON (d.object_id = s.object_id)
  7.   WHEN matched THEN
  8.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  9.   WHEN NOT matched THEN
  10.     INSERT (d.id, d.object_id, d.date_value)
  11.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)
  12.  

PostgreSQL statement:

  1.  
  2. WITH s AS (
  3.      SELECT object_id
  4.      FROM   acme_state_tmp
  5. ),
  6. upd AS (
  7.      UPDATE acme_obj_value
  8.      SET    date_value = LEAST(l_dt, d.date_value)
  9.      FROM   s
  10.      WHERE  acme_obj_value.object_id = s.object_id
  11.      RETURNING acme_obj_value.object_id
  12. )
  13. INSERT INTO acme_obj_value(id, object_id,  date_value)
  14. SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
  15. FROM   s
  16. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)
  17.  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s