如何创建可自动将文本转为小写的PostgreSQL自定义TYPE？

核心思路 #

基于PostgreSQL的TEXT类型扩展，通过自定义输入函数自动将所有输入值转为小写，输出函数直接返回存储的小写文本，同时补充跨类型比较的运算符，确保和TEXT的比较也能按小写匹配。

步骤1：创建输入输出函数 #

输入函数负责将外部字符串转为小写后存储，输出函数直接返回存储的文本：

-- 输入函数：将输入字符串转为小写
CREATE OR REPLACE FUNCTION ltext_input(cstring) RETURNS text AS $$
SELECT lower($1::text);
$$ LANGUAGE sql IMMUTABLE;

-- 输出函数：直接返回存储的小写文本
CREATE OR REPLACE FUNCTION ltext_output(text) RETURNS cstring AS $$
SELECT $1::cstring;
$$ LANGUAGE sql IMMUTABLE;

步骤2：创建LTEXT类型 #

指定输入输出函数，同时继承TEXT的特性：

CREATE TYPE ltext (
    INPUT = ltext_input,
    OUTPUT = ltext_output,
    LIKE = text
);

步骤3：自定义比较运算符 #

确保LTEXT之间、LTEXT与TEXT之间的比较能按小写匹配：

-- LTEXT之间的等于比较
CREATE OR REPLACE FUNCTION ltext_eq(ltext, ltext) RETURNS boolean AS $$
SELECT $1::text = $2::text;
$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    LEFTARG = ltext,
    RIGHTARG = ltext,
    FUNCTION = ltext_eq,
    COMMUTATOR = =,
    NEGATOR = <>
);

-- LTEXT与TEXT的等于比较（LTEXT在前）
CREATE OR REPLACE FUNCTION ltext_text_eq(ltext, text) RETURNS boolean AS $$
SELECT $1::text = lower($2);
$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    LEFTARG = ltext,
    RIGHTARG = text,
    FUNCTION = ltext_text_eq,
    COMMUTATOR = =
);

-- TEXT与LTEXT的等于比较（TEXT在前）
CREATE OR REPLACE FUNCTION text_ltext_eq(text, ltext) RETURNS boolean AS $$
SELECT lower($1) = $2::text;
$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR = (
    LEFTARG = text,
    RIGHTARG = ltext,
    FUNCTION = text_ltext_eq,
    COMMUTATOR = =
);

测试验证 #

执行以下语句验证效果：

-- 测试输入转换
SELECT 'AbC'::ltext; -- 输出 'abc'

-- 测试LTEXT之间的比较
SELECT 'AbC'::ltext = 'abc'::ltext; -- 返回 true

-- 测试LTEXT与TEXT的比较
SELECT 'AbC'::ltext = 'abc'::text; -- 返回 true
SELECT 'AbC'::ltext = 'ABC'::text; -- 返回 true

补充说明 #

• 如果需要支持更多运算符（如<, >, LIKE等），可以参照上述模式创建对应的函数和运算符。
• 该类型内部存储为TEXT，因此可以直接使用TEXT相关的函数（如length()、substring()等），无需额外适配。
• 若仅需要大小写不敏感的比较而非强制存储小写，CITEXT类型会更合适，但不符合你要求的“强制转换为lower()版本”的需求。

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