Java type in JDBC to Postgres ltree

Question

Does anyone know what Java type maps to a Postgres ltree type?

I create a table like so:

CREATE TABLE foo (text name, path ltree);

A couple of inserts:

INSERT INTO foo (name, path) VALUES ( 'Alice', 'ROOT.first.parent');
INSERT INTO foo (name, path) VALUES ( 'Bob', 'ROOT.second.parent');
INSERT INTO foo (name, path) VALUES ( 'Ted', 'ROOT.first.parent.child');
INSERT INTO foo (name, path) VALUES ( 'Carol', 'ROOT.second.parent.child');

Nothing strange there. Now I want to batch this up using a PreparedStatment:

public final String INSERT_SQL = "INSERT INTO foo( name, path) VALUES (?, ?)";

public void insertFoos(final List<Foo> foos)
{
    namedParameterJdbcTemplate.getJdbcOperations().batchUpdate(INSERT_SQL, new BatchPreparedStatementSetter()
{
  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException
  {
    ps.setString(1, foos.get(i).getName());
    ps.setString(2, foos.get(i).getPath());
  }

  @Override
  public int getBatchSize()
  {
    return foos.size();
  }
});

}

This generates the following error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO foo( name, path) VALUES (?, ?)]; nested exception is
  org.postgresql.util.PSQLException: ERROR: column "path" is of type ltree but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

Clearly I'm missing something. Why is it that I can insert 'something' using pure SQL but not JDBC?

Answer 1

This is yet another variant of the strict casting issues in PostgreSQL interacting with client drivers and ORMs that send everything they don't understand as String.

You need to use setObject with Types.OTHER, IIRC.

    ps.setObject(2, foos.get(i).getName(), Types.OTHER);

which PgJDBC should send as a bind param of type unknown. Because you're working with PgJDBC directly this is easy for you to deal with, luckily; it's a real pain when people are using ORM layers.

See:

• Macaddr/Inet type of postgres in slick
• Mapping postgreSQL JSON column to Hibernate value type
• http://www.postgresql.org/message-id/CACTajFZ8+hg_kom6QiVBa94Kx9L3XUqZ99RdUsHBFkSb1MoCPQ@mail.gmail.com

for background.

Answer 2

Why not create stored procedure and call it from CallableStatement with String param to insert row with ltree via it, if preparedStatemnt.setString() isn't working?

Other solution may be ps.setObject(2, foos.get(i).getPath(), Types.OTHER);, but I can't check it now.