Previously, my application ran on Microsoft SQL Server (MS SQL) without any issues, as its default case-insensitive behavior ensured consistent sorting results. However, after switching to PostgreSQL (PGSQL), I noticed that the sorting no longer worked as expected because PGSQL treats text comparisons as case-sensitive by default. The example below:

testsorting table

select name from testsorting order by name;

Result:
A
B
a
a1
a11
a19
a2
a20
b

This example is taken from Stack Overflow The problem is, why is the order “ABab”, when my expectation was “aAbB” or “AaBb”. This happens because PostgreSQL is case-sensitive by default, so uppercase letters (A, B) are sorted before lowercase letters (a, b).

Resolution

StringComparer.OrdinalIgnoreCase

If your codebase is small and you want to maintain case-sensitive storage while only applying case-insensitive sorting, you can use StringComparer.OrdinalIgnoreCase in LINQ’s OrderBy.

var listOfString = new List<string>
{
    "A", "a", "b", "B",
};

var orderedListOfString = users
    .Select(name => name)
    .OrderBy(name => name, StringComparer.OrdinalIgnoreCase);

// orderedListOfString: ["A", "a", "b", "B"]

citext extension

Since my application was originally running on MSSQL, using StringComparer.OrdinalIgnoreCase would require extensive code changes. To maintain consistency and minimize modifications, I prefer using the citext (case-insensitive text) extension instead. The citext (case-insensitive text) extension is a PostgreSQL data type designed to perform case-insensitive string comparisons without requiring explicit functions or modifiers (like LOWER() or ILIKE). When a column is defined as citext, PostgreSQL treats string comparisons as if they were case-insensitive by default, similar to how many other databases (like Microsoft SQL Server with its CI collations) handle text comparisons. Source: PostgreSQL To enable the citext extension in your .NET application, you can add the following code to your database configuration.

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    //db configurations...

    builder.HasPostgresExtension("citext");
}

Conclusion

When deciding between StringComparer.OrdinalIgnoreCase and PostgreSQL’s citext, the choice depends on where you want case insensitivity to be enforced. StringComparer.OrdinalIgnoreCase is ideal for small-scale, application-level case-insensitive operations, keeping the database case-sensitive and avoiding PostgreSQL-specific features. On the other hand, the citext extension is better suited for database-driven case insensitivity, especially when migrating from systems like MSSQL or when you need consistent case-insensitive behavior across queries, constraints, and indexes without modifying application code. If minimizing database changes is a priority, StringComparer offers a straightforward .NET-based solution, while citext provides a seamless, low-maintenance approach at the database level.