5

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?

Improve this question
8
  • Are you 100% sure you are connecting to the same database using the same user? Commented May 21, 2015 at 9:05
  • @RadekPostołowicz Yes I am. I currently only have one database with one user. Commented May 21, 2015 at 9:10
  • Can you post results of select current_database(), current_schema(), current_schemas(true), current_user, inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(), pg_my_temp_schema(), pg_postmaster_start_time(), session_user, version(); executed twice: once in psql and once from java code from your application? Commented May 21, 2015 at 9:39
  • @RadekPostołowicz Result from psql: "sapling , , {pg_catalog} , postgres , 192.168.0.13 , 20290 , 192.168.0.3 , 5432 , 0 , "2015-05-21 12:00:20.422757+02" , postgres , "PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu , compiled by gcc (Debian 4.7.2-5) 4.7.2 , 64-bit"" Result from java: sapling , , {pg_catalog} , postgres , 192.168.0.13 , 19955 , 192.168.0.3 , 5432 , 0 , "2015-05-21 12:00:20.422757+02" , postgres , "PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu , compiled by gcc (Debian 4.7.2-5) 4.7.2 , 64-bit" Commented May 21, 2015 at 9:48
  • I am using the JDBC41 Postgresql Driver, Version 9.4-1201 driver from the postgres HP: jdbc.postgresql.org/download.html Commented May 21, 2015 at 9:54

1 Answer 1

Reset to default
4

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 ;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks! The similarity-function works. The % operator however doesn't. Is there a way to enable the extension globally? If not, how should the % operator work?
Use ALTER EXTENSION pg_trgm SET SCHEMA pg_catalog; to use it everywhere without referencing schema dev. This will also fix your problem with % operator.
It would be better ot use alter user ... set search_path = ... to include the schema that contains the extension in the default search path

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.