Sample Header Ad - 728x90

Using collations with JSONB

5 votes
1 answer
1170 views
I have a postgres database and a case insensitive collation. The collation was created by CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false). (I also have another collation the removes accents, so ILIKE probably can't be used) In my table I have a JSONB column (here called "forms"). This is a jsonb array containing multiple keys. I now want to find the rows that have values with a specific string- case insensitively. For example, the query SELECT * FROM etymologies WHERE forms @> '[{"form": "bears"}]' ; returns 3 entries. I now want to get the same 3 entries when I search for SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]'. What would be the best way to achieve this? I tried SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]' collate case_insensitive ;, but this returns no results. (I also tried creating an index, but was super unsure about the syntax: CREATE INDEX forms_form_case_insensitive_index ON etymologies USING GIN ((forms->'form' collate case_insensitive)); did something (it ran as slow as a usual index creation), but I am not sure what exactly.)
Asked by Pux (151 rep)
Jan 1, 2023, 06:25 PM
Last activity: Jan 1, 2023, 08:37 PM