JPA Postgres query ltree path

Question

Working with Spring Boot and postgres. I have hierarchical data in the database, with the path stored in an ltree column. I'm trying to grab a particular object based on the path, but am having trouble querying the database.

model class:

@Entity
@Table(schema = "devschema", name = "family")

public class Family {

    @Id
    @Column(name = "member_id")
    private Long memId;

    @Column(name = "name")
    private String memName;

    @Column(name = "fam_path",  columnDefinition="ltree")
    private String familyPath;

    ... getters and setters
}

repository class:

  public interface OrgRepository extends PagingAndSortingRepository <Family, Long>{

    public Family findByMemId(Long id);
    public Family findByMemName(String memName);

    @Query("select f from Family f where familyPath = ?1")
    public Family findByPath(String path);
    }

A call from the controller, passing in the path as a string variable called path:

desiredMember = familyRepository.findByPath(path);

yields the following error:

2016-02-15 20:41:06.430 ERROR 88677 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: operator does not exist: devschema.ltree = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 397
2016-02-15 20:41:06.449 ERROR 88677 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception  is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is  org.hibernate.exception.SQLGrammarException:  could not extract ResultSet] with root cause

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

I've tried to cast f to text, but to no avail. Anyone have a clue on how to resolve the issue?

Answer 1

PostgreSQL docs, https://www.postgresql.org/docs/current/static/ltree.html, says that the @> operators need the gist index. And I found it significantly slower on a large dataset. (I removed it.)

So I've been using the tilde operator to my satisfaction. It doesn't require the gist index.

select * from Family where familyPath ~ '*.Smith.*'

Or if you know it's always the end of the path, leave our the last asterisk:

select * from Family where familyPath ~ '*.Smith'

Answer 2

You have selected wrong operators.

available operators like @> , <@ , etc are listed bellow :

postgres ltree documentation