Query citext column from schema using npgsql-CodePudding

I am trying to use a citext column in a simple WHERE clause from a table in a non-public schema using npgsql 6.0.5. My (simplified) database setup is

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');

Running the following C# code in a .NET6 console app only finds "test" but not "Test" (upper case T)

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");
        }
    }

}

Doing the same query using psql gives the expected result.

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

I would appreciate any hints that point me in the right direction.

CodePudding user response：

Npgsql explicitly types the parameters it sends, and when you send a string, Npgsql infers the text type. So the above is similar to writing:

select name from testtable where name = $1::text

... which wouldn't do a case-insensitive match (both sides need to be citext).

To fix this, explicitly specify the parameter type by setting NpgsqlDbType to Citext.