'Case insensitive collation still uses case sensitive comparison
According to the postgress documentation a collation can be created to ignore cases during comparison operations.
CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
create table if not exists testTable(
id int generated always as identity primary key,
test_name text collate case_insensitive not null unique
);
insert into testTable(test_name) VALUES('Foo');
select * from testTable WHERE test_name = 'foo'
The final select does not return the row as I would it expect to.
This StackExchange question mentioned that the windows icu might be old : https://dba.stackexchange.com/questions/255780/case-insensitive-collation-still-comparing-case-sensitive/255783#255783 , but even using the proposed workaround does not yield any result.
CREATE COLLATION case_insensitive(
provider = 'icu',
locale = '@colStrength=secondary',
deterministic = false
);
I do not want to set the entire collation of the database, but just for the single column.
Windows 10 Version 10.0.19044 Build 19044
PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
Solution 1:[1]
The above sample code works fine with PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit
.
It really was dependent on the postgres version even though the instructions were taken straight from the 12.9 documentation.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Kilian |