ERROR: operator class does not exist for access method "btree"

Question

I've encountered an issue while attempting to execute a script. Here's the scenario:

-- Script to create collation
CREATE COLLATION case_insensitive 
(PROVIDER = icu, LOCALE ='und-u-ks-level2', DETERMINISTIC = FALSE);

-- Followed by adding an index using the collation
CREATE UNIQUE INDEX col1_idx ON my_table (col1 case_insensitive);

Regrettably, during the indexing process, I encountered the following error:

SQL Error [42704]: ERROR: operator class "case_insensitive" does not exist for access method "btree"

I have checked into pg_opclass table and it wasn't exist there:

SELECT * FROM pg_opclass WHERE opcname = 'case_insensitive';

Any idea what's I'm doing wrong/missing here? I would be immensely grateful for any insights or assistance regarding this.

Answer 1

Syntax (col1 case_insensitive) is used for operator classes: varchar_pattern_ops or gin_trgm_ops as examples of commonly used ones.

Specific collation in indexes can be used with COLLATE keyword:

CREATE UNIQUE INDEX col1_idx ON my_table (col1 COLLATE "case_insensitive");

---

One can specify both collate and operator class. These are different features.

CREATE UNIQUE INDEX col2_idx ON my_table (col1 collate "case_sensitive" text_pattern_ops);