Native Query for strict_word_similarity is not working

49 views Asked by At

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);
0

There are 0 answers