Equivalent of UTF8_UNICODE_CI collation in PostgreSQL
In Postgres, you would typically solve this differently.

For starters, just use ILIKE for case insensitive matching. The manual:


  The key word ILIKE can be used instead of LIKE to make the match
  case-insensitive according to the active locale. This is not in the
  SQL standard but is a PostgreSQL extension.


WHERE name ILIKE '%lala%'


You do not need a different collation for this. Trigram indexes support both case sensitive and insensitive pattern matching:


LOWER LIKE vs iLIKE
How is LIKE implemented?


Or use the more portable lower(col) LIKE

WHERE lower(name) LIKE '%lala%'  -- pattern must be lower case, too.



Generic Ruby solution for SQLite3 “LIKE” or PostgreSQL “ILIKE”?


Or you could use the special data type citext provided by the additional module citext:


Index on column with data type citext not used


If you insist on a special collation, you can create your own:


Add a new collation to a Postgres database


I would not do that.

If you want to take string "normalization" one step further (remove all diacritic signs), look to unaccent():


Does PostgreSQL support “accent insensitive” collations?


You can combine both, instructions in the linked answer. More here:


PostgreSQL accent + case insensitive search


Overview:


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL




I would just use ILIKE and support it with a trigram index for big tables. Preferably use the latest version Postgres 9.6. It has several improvements for trigram GIN indexes.