Oracle Upsert预编译语句迁移至PostgreSQL的问题排查

Hey there, converting Oracle's MERGE to PostgreSQL's upsert syntax has a few gotchas, so let's break down what's off with your attempt first, then walk through the correct solution.

What's Wrong With Your Attempted Query #

Let's go through the issues one by one:

• Broken INSERT syntax: You tried wrapping a SELECT subquery inside VALUES(), which isn't valid in PostgreSQL. When inserting from a subquery, you need to use INSERT INTO ... SELECT ... directly—VALUES() is meant for explicit row values, not wrapping subqueries.
• Misused aliases: You gave your subquery the alias b, but then referenced a in the DO UPDATE clause. PostgreSQL doesn't use table aliases like a here for the target table in an upsert. Instead, we use the special EXCLUDED keyword to access the row that would've been inserted if there was no conflict.
• (Optional) Unnecessary dual table: While PostgreSQL does support FROM dual for Oracle compatibility, it's not required—you can select values directly without it, but we'll keep it in the fix since you wanted to use it.

Correct PostgreSQL Upsert Query (With dual Table) #

Here's the properly converted version that matches your original Oracle MERGE behavior:

INSERT INTO DUMMY(id, name, size)
SELECT ? AS id, ? AS name, ? AS size FROM dual
ON CONFLICT(id) DO UPDATE
SET 
  id = EXCLUDED.id,
  name = EXCLUDED.name,
  size = EXCLUDED.size;

A Quick Explanation: #

• EXCLUDED is your friend: This keyword refers to the row you tried to insert but that hit a conflict on the id column. It replaces the b alias from your Oracle MERGE—it's how you access the new values you want to update the existing row with.
• Simplified Version (No dual): If you don't need to keep dual for compatibility, you can make this even cleaner:

  INSERT INTO DUMMY(id, name, size)
  VALUES(?, ?, ?)
  ON CONFLICT(id) DO UPDATE
  SET 
    id = EXCLUDED.id,
    name = EXCLUDED.name,
    size = EXCLUDED.size;
  

• Behavior Match: Both versions do exactly what your original Oracle MERGE did: if a row with the given id exists, it updates all columns with the new values; if not, it inserts a new row.

内容的提问来源于stack exchange，提问作者yed2393