无法转换ltree JPARepository

Question

原生查询:

1. SELECT * from test where path <@ 'a.b.c' and path <> 'a.b.c';
2. SELECT * FROM test WHERE path ~ '*.b.c.*{1}'

JPA:

@Query(value = "select * from test where path <@ 'a.b.c' and path <> 'a.b.c'", nativeQuery = true)
List<Test> getAllPath(@Param("pathToSearch") String pathToSearch);

我想用pathToSearch param代替a.b.c

错误：

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

Answer 1

它起作用了

@Query(value = "SELECT * FROM test WHERE path <@ CAST(:pathToSearch AS ltree) and path <> CAST(:pathToSearch AS ltree)", nativeQuery = true)