I am trying to use strict_word_similarity of PostgreSQL to find difference between two words in java spring boot thru native query but it is giving me below error. I am unable to solve the error.
@Query(
nativeQuery = true,
value =
"CREATE EXTENSION IF NOT EXISTS pg_trgm;"
+ "select cast(c.customer_master_id as varchar) as customer_master_id "
+ "from customer_account_service.customer c where c.dob=:dob and "
+ "c.gender=:gender and "
+ "(strict_word_similarity(CAST(:fullName AS text), CAST(c.full_name AS text)) < :matchPercent or strict_word_similarity(REPLACE( CAST(c.full_name AS text),' ','') ,REPLACE(CAST(:fullName AS text),' ','')) = 1)")
Set<String> searchCustomerEntitiesByNameDobAndGender(
@Param("fullName") String fullName,
@Param("dob") Timestamp dob,
@Param("gender") String gender,
@Param("matchPercent") Integer matchPercent);
Caused by: org.postgresql.util.PSQLException: ERROR: function strict_word_similarity(text, text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Fortunately i was able to use levenshtein function similarly but not able to use strict_word_similarity
@Query(
nativeQuery = true,
value =
"select cast(c.customer_master_id as varchar) as customer_master_id "
+ "from customer_account_service.customer c where c.dob=:dob and "
+ "((levenshtein(:fullName, c.full_name) * 100) / greatest(:nameLength, length(c.full_name))) < :matchPercent")
Set<String> searchCustomerEntitiesByNameAndDob(
@Param("fullName") String fullName,
@Param("dob") Timestamp dob,
@Param("nameLength") Integer nameLength,
@Param("matchPercent") Integer matchPercent);