如何在PostgreSQL中创建含LOWER的GIN索引实现大小写不敏感搜索？

首先咱们先排查你遇到的原生SQL查询异常问题，再给出适配EclipseLink的JPA解决方案。

一、为什么LOWER(name) LIKE '%full%'没返回结果？ #

你插入的name是'User Full Name'，正常情况下LOWER(name)会得到'user full name'，LIKE '%full%'肯定能匹配到这条数据。出现无结果的情况，大概率是以下原因之一：

1. 搜索词大小写错误
   如果你实际执行的是LOWER(name) LIKE '%Full%'（注意Full首字母大写），PostgreSQL的LIKE是严格区分大小写的，自然匹配不到转成小写的字段值。必须确保搜索词也是小写的。

2. 数据存在特殊字符
   比如插入的'User Full Name'中，Full前面的空格是全角空格，转小写后会变成'user　full name'（全角空格），而你用的'%full%'是半角通配符，就会匹配失败。可以执行这条SQL确认实际值：

   SELECT id, name, LOWER(name) FROM users;
   

3. 拼写或语法错误
   检查是否漏写了通配符%，或者使用了全角的%、_等符号，这些都会导致匹配失败。

二、EclipseLink下实现不区分大小写搜索的正确方案 #

因为EclipseLink的JPQL不支持ILIKE语法，我们可以通过同时对字段和搜索参数转小写的方式实现，还能复用你创建的GIN索引保证性能。

1. 实体类映射（确保正确） #

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "name", nullable = false, length = 512)
    private String name;
    
    // getter、setter 省略
}

2. JPA查询代码示例 #

核心是把用户输入的搜索词也转成小写，再和转小写的字段做LIKE匹配：

// 用户输入的关键词，可能是任意大小写（比如"Full"、"FULL"）
String userInput = "Full";
// 转小写并拼接通配符
String likePattern = "%" + userInput.toLowerCase() + "%";

EntityManager em = ...; // 获取EntityManager
TypedQuery<User> query = em.createQuery(
    "SELECT u FROM User u WHERE LOWER(u.name) LIKE :pattern", User.class
);
query.setParameter("pattern", likePattern);

List<User> result = query.getResultList();

3. 索引的作用 #

你创建的users_name_idx索引（GIN (LOWER(name) gin_trgm_ops)）会被PostgreSQL自动使用，避免全表扫描，大幅提升模糊查询的性能，这个配置是完全正确的。

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