Spring Boot调用PostgreSQL strict_word_similarity函数报错求助

问题背景 #

在Spring Boot项目中使用原生SQL查询调用PostgreSQL的strict_word_similarity函数时，出现报错：

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.

但相同场景下使用levenshtein函数可以正常运行。

原因分析 #

1. pg_trgm扩展未正确安装：strict_word_similarity属于pg_trgm扩展提供的函数，但原代码中将CREATE EXTENSION与SELECT语句放在同一个@Query中，Spring Data JPA原生查询默认不支持多语句执行，导致扩展未被创建。
2. 函数模式未明确指定：若pg_trgm扩展安装在public模式，而业务表在自定义模式（如customer_account_service）下，数据库可能无法在当前模式找到该函数。
3. PostgreSQL版本过低：strict_word_similarity是PostgreSQL 12及以上版本的pg_trgm扩展新增函数，低版本不支持。

解决方案 #

1. 手动安装pg_trgm扩展 #

通过psql、pgAdmin等工具，在应用连接的目标数据库中执行以下语句：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

不要将该语句放在Spring Data JPA的@Query中，避免多语句执行限制。

2. 明确指定函数所在模式 #

修改原生查询语句，为strict_word_similarity函数加上模式前缀（默认public），确保数据库能找到函数：

@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 "
            + "c.gender=:gender and "
            + "(public.strict_word_similarity(CAST(:fullName AS text), CAST(c.full_name AS text)) < :matchPercent "
            + "or public.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);

3. 检查PostgreSQL版本 #

若上述操作后仍报错，检查数据库版本是否低于12。如果是，需升级PostgreSQL至12及以上版本，或改用similarity（pg_trgm提供）、levenshtein等兼容低版本的相似性计算函数。

内容的提问来源于stack exchange，提问作者harsh agarwal