Enable PostgreSQL extensions in JDBC

Question

I am using Spring + Tomcat8 together with PostgreSQL 9.2. I installed the Trigram extension on the database for improved searching in tables.

When I connect to my database manually, the following statement works:

SELECT id, name, similarity(name, 'a') FROM dev.customer WHERE name % 'a' ORDER BY similarity ;

However, when I try this in Spring using jdbcTemplate.query() I get an error:

PSQLException: ERROR: function similarity(character varying, character varying) does not exist

When I remove the similarity() function and only use the % operator, I get the following exception:

ERROR: operator does not exist: character varying % character varying

It seems that the postgres jdbc driver needs to be configured in order to support non-standard syntax: https://jdbc.postgresql.org/documentation/81/ext.html

The server.xml of my tomcat installation contains the following resource:

<Resource name="jdbc/NasPostgresDB" auth="Container" type="javax.sql.DataSource"
          username="usr" password="pwd"
          url="jdbc:postgresql://127.0.0.1/dbname"
          driverClassName="org.postgresql.Driver"
          initialSize="5" maxWait="5000"
          maxActive="120" maxIdle="5"
          validationQuery="select 1"
          poolPreparedStatements="true"/>

which should be correct: https://jdbc.postgresql.org/documentation/81/load.html

What exacly do I need to do to make trigram matching working?

Answer 1

In PostgreSQL, every object (be it a table, an index or a function) belongs to a schema. As you said in comment that you installed the extension with :

SET SCHEMA 'dev'; CREATE EXTENSION pg_trgm;

I assume that the functions are accessible in dev shema.

If it is the case, you should be able to use them in JDBC that way :

SELECT id, name, dev.similarity(name, 'a') as similarity FROM dev.customer 
    WHERE name % 'a' ORDER BY similarity ;