Equivalent of UTF8_UNICODE_CI collation in PostgreSQL
8
votes
1
answer
17778
views
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.)
Asked by Daniel Gray
(699 rep)
Dec 1, 2016, 03:54 PM
Last activity: Aug 1, 2023, 06:38 PM
Last activity: Aug 1, 2023, 06:38 PM