Converting Oracle upsert to PostgreSQL prepared statement

user_12328682 0
Viewed 107

I want to migrate upsert queries from Oracle DB to PostgreSQL. Below shows a Oracle prepared statement that takes values from dual and does an upsert operation on DUMMY table.

MERGE INTO DUMMY a
USING (SELECT ? ID,
              ? NAME,
              ? SIZE from dual) b
ON (a.ID = b.ID)
WHEN MATCHED THEN
    UPDATE
    SET a.ID = b.ID,
        a.NAME = b.NAME,
        a.SIZE = b.SIZE
WHEN NOT MATCHED THEN
    INSERT(a.ID,
           a.NAME,
           a.SIZE)
    VALUES ( b.ID,
             b.NAME,
             b.SIZE)

I want to convert that into PostgreSQL using dual. Below is my PostgreSQL attempt, but I cannot use select statement in the using statement. Can you point out where the problem is?

INSERT INTO DUMMY(id,name,size)
values (SELECT ? id,
               ? name,
               ? size from dual) b
ON conflict(id)
do UPDATE
    SET a.id= b.id,
        a.name=b.name,
        a.size=b.size;
database-migration prepared-statement jdbc upsert postgresql
1 Answers

No need for aliases of the values. You can reference those values using the excluded keyword:

INSERT INTO DUMMY(id,name,size)
values (?, ?, ?)
ON conflict(id)
do UPDATE
    SET name = excluded.name,
        size = excluded.size;

Note that I removed the update of the ID column as it is useless because by definition the new value must be the same as the existing.

user_3303150
Related