What is the JDBC type for ctid in postgres?

Question

I cannot show the correct type in the prepared statement. This code:

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(); 

throws this exception:

 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 

Note that from psql, removal works using the line:

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

What is the correct type / encoding for tid in JDBC PreparedStatement? I tried setRowId () (throws ava.sql.SQLFeatureNotSupportedException: method org.postgresql.jdbc4.Jdbc4PreparedStatement.setRowId (int, RowId) is not implemented yet.) And setBytes () (throws ... operator does not exist): tid = b

Answer 1

Solved! You must manually create the PGO object and set the type and value and pass it to JDBC as the object. Now it works:

 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);