I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The
SELECT statements used by this program can not be altered.
The abstract problem is that we somehow need this search to be case insensitive but it currently is case sensitive.
I read that Postgres 12 does support non-deterministic collations which allow for this behavior.
I have a Postgres Server (Version PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit) installed on a German Windows machine.
So for testing purposes, I created a new database to test on:
CREATE DATABASE collation_test WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;
In this database I created the following collation I found in an article about these collations
CREATE COLLATION collat_ci ( provider = 'icu', locale = 'und-u-ks-level2', deterministic = false );
After this, I needed a table to test this collation with
CREATE TABLE public.person ( "Id" bigint NOT NULL, "Name" text COLLATE public.collat_ci, PRIMARY KEY ("Id") ); ALTER TABLE public.person OWNER to postgres; INSERT INTO person VALUES (1, 'Robin'), (2, 'robin');
So now I tried the following select query:
SELECT ( SELECT "Name" FROM person p1 WHERE p1."Id" = 1 ) = 'Robin';
true as expected as the text in the database and the literal given match completely.
But if I try the same with a lower case
r which I would expect to match because of my collation but it still returns
SELECT ( SELECT "Name" FROM person p1 WHERE p1."Id" = 1 ) = 'robin';
When trying to compare both inserted rows' name to each other I still got
false as a result:
SELECT ( SELECT "Name" FROM person p1 WHERE p1."Id" = 1 --'Robin' ) = ( SELECT "Name" FROM person p2 WHERE p2."Id" = 2 --'robin' );
Does anyone know why my comparison does not behave as expected and how to get it to do so?