I'm trying to fetch a tree from postgres DB using a native query, the next query is working fine using psql terminal:
SELECT col_1 FROM my_tree WHERE parent_id ~ lquery('*.C.*')
but when I add the same query using entity manager:
private List<String> fetchTreeByParentId() {
Query query = entityManager.createNativeQuery("SELECT col_1 FROM my_tree WHERE parent_id ~ lquery('*.C.*')");
return query.getResultList();
}
I'm getting next error:
2021-04-28 08:21:51.105 WARN 107978 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42883
2021-04-28 08:21:51.105 ERROR 107978 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: function lquery(unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 65
I there something I'm missing ... maybe update the postgresql jar?
The project is spring boot 2.4.2
So thanks to @jjanes I noticed that data types like
lquery
<ree
are in my db schema so I had to change my code to:1.- Get nodes using lquery to use patterns like '.C.'
2.- Get nodes using ltree to use the label of node e.g. 'C'
I you have the ltree extension in
public
schema there is no need to add{h-schema}
andOPERATOR
, I was having the issue because I'm not working with public schema