Postgresql upsert with JDBC parameter markers

问题:

I've got an INSERT statement that works today when called from JDBC using parameters markers:

INSERT INTO errorqueue as eq (jobname, sourceid, item) VALUES(?,?,?)

In my Java code, I bind the parameters:

Connection connection=null;
PreparedStatement stmt=null;
try {

    connection = getConnection();
    stmt = connection.prepareStatement(sqlInsert);

    stmt.setString(1, this.jobName);
    stmt.setString(2, errorItem.getId());
    stmt.setString(3, item.getBody());
    stmt.executeUpdate();
} catch () {
...
}

I'm struggling with how I'd need to handle the parameters if I convert this to an UPSERT:

INSERT INTO errorqueue as eq (jobname, sourceid, item) VALUES(?,?,?) ON CONFLICT (jobname,sourceid) UPDATE eq SET item=? Where jobname=? and sourceid=?;

It's sneaky subtle, but in the INSERT the parameter order is (a,b,c) but in the update, the paramter binding needs to be (c,a,b)

回答1:

You don't need parameters in the on conflict part at all. Just use set item = excluded.item. And you also don't need a where clause for the update:

INSERT INTO errorqueue as eq 
   (jobname, sourceid, item) 
VALUES(?,?,?) 
ON CONFLICT (jobname,sourceid) 
  UPDATE SET item=exluded.item;

You can leave your Java code as it is and Postgres will take care of matching the correct rows.

回答2:

The answer #1 is wrong.

INSERT INTO tablename (column1, column2, value) VALUES (?,?,?) ON CONFLICT (column1, column2) DO UPDATE SET value = excluded.value";

the "eq" in the above answer does not belong there. column1 and column2 belong to some constraint, the insert above will update column1, column2 and value even though the columns1&2 are not listed explicitely.