Equivalent of UTF8_UNICODE_CI collation in PostgreSQL I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried:

CREATE TABLE thing (
    id    BIGINT PRIMARY KEY
   ,name  VARCHAR(120) NOT NULL COLLATE "UTF8_UNICODE_CI"
);


but I get:


ERROR: collation "UTF8_UNICODE_CI" for encoding "UTF8" does not exist



Reading around, I found that the pg_collation table displays collations, which showed:

=# SELECT * from pg_collation;
 collname | collnamespace | collowner | collencoding | collcollate | collctype
----------+---------------+-----------+--------------+-------------+-----------
 default  |            11 |        10 |           -1 |             |
 C        |            11 |        10 |           -1 | C           | C
 POSIX    |            11 |        10 |           -1 | POSIX       | POSIX
(3 rows)


So it's clearly not there... Is there any way to add it, or obtain the behavior I want? (I want a search WHERE name LIKE '%lala%' to match 'lalá', 'LÂLÄ', etc.)
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.