java - postgres中ctid的JDBC类型是什么？

我似乎无法在准备好的语句中设置适当的类型。这段代码:

String sql = "delete from foo where ctid = ?";
PreparedStatement deleteStmt = conn.prepareStatement( sql );
deleteStmt.setString(1, "(0,43)");  // select ctid from foo shows (0,43) exists....
int a = deleteStmt.executeUpdate();

抛出此异常:

org.postgresql.util.PSQLException: ERROR: operator does not exist: tid = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.   Position: 28

请注意，在psql中，删除使用字符串进行工作:

mydb=# DELETE FROM foo where ctid = '(0,43)';
DELETE 1

JDBC PreparedStatement中的tid正确的类型/编码是什么？我已经尝试过setRowId()(引发ava.sql.SQLFeatureNotSupportedException:方法org.postgresql.jdbc4.Jdbc4PreparedStatement.setRowId(int，RowId)尚未实现。)和setBytes()(引发...运算符不存在:tid =字节)

最佳答案

解决了!您必须手动创建PGO对象并设置类型和值，并将其作为对象传递给JDBC。现在可以使用:

sql = "delete from foo where ctid = ?";
deleteStmt = conn.prepareStatement( sql );
org.postgresql.util.PGobject pgo = new org.postgresql.util.PGobject();
pgo.setType("tid");
pgo.setValue("(0,54)");  // value is a string as might be returned in select ctid from foo and then resultSet.getString(1);
deleteStmt.setObject(1, pgo);

int a = deleteStmt.executeUpdate();
System.out.println("delete returns " + a);