Database agnostic command to update if exists otherwise insert(UPSERT) Spring boot

Question

I have a query in oracle that basically updates activate flag to yes if exists and add's new entry with active flag yes if not exists. This query does not play well with my postgres transition work I am doing. I tried looking but could not find a query that would work for both cases.

Does someone have an example of this that would work on both postgres and oracle ?

Some Context:

oracle sql: update if exists else insert

here is my existing Oracle only query in java

 private static final String SQL_UPDATE_OR_INSERT_USER ="
DECLARE 
  u_name users.name%type := (:name); 
  u_key users.ukey%type := (:ukey); 
BEGIN  
  UPDATE users SET ACTIVE=1 WHERE ukey IN (u_key) and  lower(name) = lower(u_name); 
  IF ( sql%rowcount = 0 ) THEN 
    INSERT INTO users(user_id, ukey, name, ACTIVE) VALUES 
    (hibernate_sequence.nextval, u_key, u_name, 1); 
  END IF;   
END;";

This does not work with postgres and postgres one looks very different from this one.

The SQL Standard specifies the `MERGE` statement exactly for your type of situation. Note that this is a SQL statement; what you wrote is PL/SQL code, which has zero chance of being "agnostic". Even with `MERGE` - while the standard specifies it, I don't know which db products do actually support it and which don't; Oracle does, so in Oracle you should use it (not the PL/SQL code you have now). — , Sep 09 '21 at 00:56
@mathguy this is legacy code I am fixing . Also my DB knowledge is abysmal at best I will try to refactor this to user mereg. — Praveen, Sep 09 '21 at 01:01
@SayanMalakshinov: Postgres does not support the MERGE statement. Don't use random findings on the internet (in this case the _discussion_ about implementing it) as a reference. The only authoritative source if a specific command is supported is [the manual](http://www.postgresql.org/docs/current/static/sql-commands.html) — , Sep 09 '21 at 05:10
@a_horse_with_no_name hey I am trying to use INSERT INTO ON CONCFLICT. But my issue is I want to check multiple rows for a column . Is there a way to put where clause inside conflict condition — Praveen, Sep 09 '21 at 18:11

score 0 · Accepted Answer · answered Sep 09 '21 at 23:04

Despite Standards there are cases (some would say most) where there is no Database agnostic process for a task. Unfortunately for you this is one of them. About the closest you can get in Postgres for the Oracle is: (see lines with --<<<)

DO $$                                            --<<< added
    DECLARE 
      u_name users.name%type := (:name); 
      u_key users.ukey%type := (:ukey); 
      sql_rowcount integer;                      --<<< added
    BEGIN  
      UPDATE users SET ACTIVE=1 WHERE ukey IN (u_key) and  lower(name) = lower(u_name); 
      
      GET DIAGNOSTICS sql_rowcount = ROW_COUNT;  --<<< added 
     
      IF ( sql_rowcount = 0 ) THEN               --<<< changed 
        INSERT INTO users(user_id, ukey, name, ACTIVE) VALUES 
        (hibernate_sequence.nextval, u_key, u_name, 1); 
      END IF;   
    END;
$$;                                              --<<< added

Database agnostic command to update if exists otherwise insert(UPSERT) Spring boot

1 Answers1