Npgsql 6.0.5查询非public schema下citext字段大小写匹配异常

问题描述 #

使用Npgsql 6.0.5版本操作PostgreSQL数据库时，对非public schema下数据表的citext类型字段执行带WHERE子句的查询，出现匹配结果不符合预期的问题：仅能匹配大小写完全一致的记录，citext的大小写不敏感特性失效。
数据库初始化脚本如下：

create schema citest;
create table citest.testtable (
  id serial primary key,
  name public.citext
);
set search_path = citest,public;
insert into testtable (name) values ('test');

在.NET 6控制台应用中运行如下C#代码，查询值为"test"时能返回结果，查询值为"Test"时无法匹配到记录：

class Program {

    static async Task Main(string[] args) {
        string connectionString = "Server=myserver;Port=5432;Username=user;Password=pass;Database=mydatabase;Search Path=citest,public;SSLMode=Require;Trust Server Certificate=true;";
        await SelectQueryTest(connectionString, "test");
        await SelectQueryTest(connectionString, "Test");

        Console.WriteLine("DONE");
    }

    private static readonly string SelectQuery =
        "select name from testtable where name = $1;";

    static async Task SelectQueryTest(string connString, string text) {
        await using var connection = new NpgsqlConnection(connString);
        await connection.OpenAsync();

        await using var cmd = new NpgsqlCommand(SelectQuery, connection) {
            Parameters =
            {
                new() { Value = text }
            }
        };
        await using var reader = await cmd.ExecuteReaderAsync();
        if (await reader.ReadAsync()) {
            var reply = reader.GetFieldValue<string>(0);
            Console.WriteLine($"postgres query {text}: read {reply}");
        } else {
            Console.WriteLine($"postgres query {text}: read none");
        }
    }

}

相同查询逻辑在psql客户端中执行时，citext大小写不敏感特性正常，查询"Test"能正确返回存储的"test"记录：

set search_path = citest,public;
select name from testtable where name = 'Test';
 name
------
 test
(1 row)

问题根因 #

核心原因是Npgsql 6.0.5版本默认不会自动加载非public schema下的表字段类型映射信息，虽然citext扩展安装在public schema，但表建在自定义的citest schema下，驱动生成查询参数时，无法识别对应字段的citext类型，默认将传入的字符串参数按标准text类型传递。
PostgreSQL执行citext类型字段 = text类型参数的比较时，由于text类型优先级高于citext，会将citext字段隐式转换为text类型做大小写敏感的等值比较，因此无法匹配大小写不一致的记录。而psql中直接写入的字符串字面量是无类型值，数据库会自动将其隐式转换为左值字段的citext类型，因此能正常触发大小写不敏感匹配。

排查方向 #

• 开启Npgsql调试日志，查看查询绑定的参数类型，会看到传入的$1参数类型为text而非预期的citext
• 开启PostgreSQL全量语句日志，查看驱动实际发送的参数绑定元数据，可直接确认参数类型不匹配的问题

解决方案 #

• 方案1：手动指定参数类型
  这是改动量最小的方案，创建NpgsqlParameter时显式声明参数类型为Citext，不需要调整全局配置：

  new() { 
      Value = text,
      NpgsqlDbType = NpgsqlDbType.Citext
  }
  

• 方案2：配置驱动类型加载搜索路径
  在原有连接字符串中添加Load Table Composites Search Path=citest,public配置，让Npgsql初始化连接时加载指定schema下的表字段类型映射，驱动会自动识别citext字段类型，无需逐个参数指定类型。
• 方案3：SQL层显式转换参数类型
  直接调整查询语句，将参数显式转换为citext类型，完全不依赖驱动的类型映射逻辑，兼容性最好：

  select name from testtable where name = $1::public.citext;
  

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