Case insensitive collation still uses case sensitive comparison-CodePudding

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

CodePudding user response：

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.