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.)
create collation
postgresql.org/docs/9.1/static/sql-createcollation.html