![]() ![]() If you are using PostgreSQL v12 or better, and PostgreSQL was configured -with-icu, you can define a case-insensitive collation like this:įor more details about ICU collations, read my article on that topic. regular expression matching is not case insensitive, and you have to use the case insensitive operator ~* explicitly.performance for longer values can also be bad, because citext internally calls lower(col COLLATE "default") before comparing the values. ![]() there is no data type civarchar, so you can only implement that with a check constraint.That is simple and convenient, but has some disadvantages as well: The extension citext provides a data type citext, which stands for “case-insensitive text”. if the database column contains long values, the whole value has to be converted to lower case, even if only a few characters have to be compared, which leads to bad performance.the solution is implemented at the application level, that is, you have to custom-tailor the query for case-insensitive search.This can be made fast with a B-tree index on lower(col), but has two disadvantages: WHERE lower(col) = lower('search string') There are three known solutions to case-insensitive search in PostgreSQL: Explicit conversion with lower() or upper()Ī query that uses this method would look as follows: So let’s have a closer look at the problem and at possible solutions. This works like a charm, except if you want to perform pattern matching. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. ![]() Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |