如何诊断PostgreSQL 15中下划线通配符查询失效问题？

问题根源 #

你使用了CHAR(20)作为字段类型，这是固定长度字符类型，当插入的字符串长度小于20时，PostgreSQL会自动在字符串末尾填充空格，直到总长度达到20。也就是说，你的'Joe'实际存储的是'Joe' + 17个空格，'Zoe'是'Zoe' + 17个空格。

而LIKE '_oe'的匹配逻辑是：寻找恰好3个字符，且第一个字符任意、后两个是'oe'的字符串。但实际存储的字符串长度是20，自然无法匹配，所以返回空结果。

解决方案 #

方案1：修改字段类型为可变长度（推荐）

名字这类长度不固定的内容，更适合用VARCHAR(n)类型存储，它不会自动填充空格：

-- 直接修改现有表的字段类型
ALTER TABLE people ALTER COLUMN names TYPE VARCHAR(20);

-- 或者重新建表（如果数据量不大）
DROP TABLE IF EXISTS people;
CREATE TABLE people (
    names VARCHAR(20)
);
INSERT INTO people VALUES ('Joe'), ('John'), ('Johanna'), ('Zoe');

修改后再执行你的查询SELECT * FROM people WHERE names LIKE '_oe';，就能得到预期的结果。

方案2：查询时去除尾部空格

如果不想修改表结构，可以用TRIM()函数去掉字段值的尾部空格后再匹配：

SELECT * FROM people WHERE TRIM(names) LIKE '_oe';

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