What is the JDBC type for ctid in postgres?

Question

I cannot seem to set the proper type in a 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, the delete works using a string:

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

What is the proper type/encoding for a tid in a JDBC PreparedStatement? I have tried setRowId() (throws ava.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setRowId(int, RowId) is not yet implemented.) and setBytes() (throws ...operator does not exist: tid = byte)

What if you try with deleteStmt.setObject(1, "(0,43)");? What exception does it throw? — dic19
– dic19, Commented Sep 17, 2013 at 14:42
Another possible solution: setString(1, "(0,43)::ctid") or don't use a PreparedStatement and use a dynamic SQL. — user330315
– user330315, Commented Sep 17, 2013 at 14:46
Both setObject(1, "(0,43)") and setString(1, "(0,43)::ctid") throw org.postgresql.util.PSQLException: ERROR: operator does not exist: tid = character varying. Dynamic SQL with no substitutions on a PreparedStatement (e.g. sql = "delete from foo where ctid = '(0,50)'"; deleteStmt = conn.prepareStatement( sql ); ) DOES work. — Buzz Moschetti
– Buzz Moschetti, Commented Sep 17, 2013 at 15:01
ctid is an internal column of type tid and cannot be changed. — Buzz Moschetti
– Buzz Moschetti, Commented Sep 18, 2013 at 15:55

Buzz Moschetti · Accepted Answer · 2013-09-18 16:28:30Z

3

Solved! You have to manually create a PGO object and set the type and value and pass it to JDBC as a object. This now 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);

answered Sep 18, 2013 at 16:28

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wech · Accepted Answer · 2018-06-01 14:21:14Z

1

Old topic, but I would like to add that writing

where ctid = ?::tid";

instead of

where ctid = ?::ctid";

did the trick. It is then possible to use PreparedStatement.setString(x, "(10,3)")

answered Jun 1, 2018 at 14:21

wech

915 bronze badges

Collectives™ on Stack Overflow

What is the JDBC type for ctid in postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related