2

I have database with PostGIS already installed. I am running the following query from pgAdmin and I am successfully able to run it without any errors.

But when I run it with Java, I am getting the following error:

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

org.postgresql.util.PSQLException: ERROR: operator does not exist: public.geometry && public.geometry

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

 07-06-2021 17:19:13.293 [http-nio-8083-exec-2]
            ERROR o.a.c.c.C.[.[.[.[dispatcherServlet].log - Servlet.service() for servlet [dispatcherServlet] in context with path [] 
            threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from aut_line l where (public.ST_GeomFromText('POLYGON ((13.1862251 47.2411128, 13.186196 47.240849, 13.1860919 47.2405941, 13.1859168 47.240358, 13.1856774 47.2401497, 13.185383 47.2399771, 13.1850448 47.239847, 13.1846758 47.2397644, 13.1842902 47.2397323, 13.1839029 47.2397521, 13.1835286 47.239823, 13.1831819 47.2399422, 13.182876 47.2401052, 13.1826226 47.2403058, 13.1824316 47.2405361, 13.1823102 47.2407874, 13.1822631 47.24105, 13.1822922 47.2413138, 13.1823963 47.2415686, 13.1825714 47.2418048, 13.1828107 47.2420131, 13.1831052 47.2421857, 13.1834434 47.2423158, 13.1838124 47.2423984, 13.184198 47.2424305, 13.1845854 47.2424107, 13.1849596 47.2423398, 13.1853063 47.2422206, 13.1856123 47.2420575, 13.1858656 47.241857, 13.1860566 47.2416267, 13.186178 47.2413754, 13.1862251 47.2411128))',4326) && way)]; 
            nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: public.geometry && public.geometry
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 822] with root cause
org.postgresql.util.PSQLException: ERROR: operator does not exist: public.geometry && public.geometry
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 822
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

i can see the following operator details operator details

search_path as per details bellow

seach path

2
  • 2
    Why is there a postgis_schema when the geometry type is in public? And what is the search path in Java? – CL. Jun 7 at 13:41
  • @CL postgis_schema is just one schema and search path did not defined in connection URL : url: jdbc:postgresql://localhost:5432/local_db?currentSchema=ggg_new&stringtype=unspecified – swan Jun 8 at 2:48
4

Thanks @CL for Hint, search_path in java solved the problem, you can add comma separated multiple schema in currentSchema connection string parameter

old connection url

url: jdbc:postgresql://localhost:5432/local_db?currentSchema=ggg_new&stringtype=unspecified

Updated connection String that solved problem

url: jdbc:postgresql://localhost:5432/local_db?currentSchema=ggg_new,public&stringtype=unspecified

Ref: https://jdbc.postgresql.org/documentation/head/connect.html

currentSchema = String

Specify the schema (or several schema separated by commas) to be set in the search-path. 
This schema will be used to resolve unqualified object names used in statements over this connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.