PostgreSQL collation 설정(대소문자/악센트/전각반각 등, 정렬순서)

추가내용,..

LIKE 검색이나, 문자열 함수 등을 사용이 불가능해집니다.

 

PostgreSQL 에서 Collation 관련 이슈..

 

https://www.postgresql.org/docs/current/collation.html : PG 메뉴얼

https://www.unicode.org/reports/tr10/  : Unicode® Technical Standard #10

https://unicode-org.github.io/icu/ : ICU 문서

https://webcache.googleusercontent.com/search?q=cache:W2Mex4SS0ygJ:https://postgresql.verite.pro/blog/2019/10/14/nondeterministic-collations.html&cd=1&hl=ko&ct=clnk&gl=kr 

 

PostgreSQL 에서는 대소문자 등 구분하는 문제로 엄청 골치아펐는데.. 알고보니.. 컬레이션을 지원한다!!!! 
오히려 컬레이션에 관해서는 maria나 ms 보다 비교도 안되게 많은 것을 지원하는것 같다.. (힌트는 진짜 지원하지 않는다 ㅋㅋ. )...
수많은 곳을 검색하고 메뉴얼대로 해도 안되서, 포기하고 있었다..

수많은 곳에서 ilike 또는 upper 와 FBI 만 알려주고. 메뉴얼대로 해도 안되서 반포기상태였다.(오죽했으면 1년만에 티스토리에 로그인을 해서 글을 남기고 있다.....)

ilike, upper, fbi, mapping 방식들은 계속 검색이나 성능 문제들이 계속 나올것이고.. (꼼수를 쓰면 계속 발목 잡힌다..)
글로벌 서비스인경우 악센트, 전각반각, 일본어(히나가라/카나)는 해결할 수 없다.  (다른 dbms는 모두 컬레이션 설정을 통해 설정할 수 있다.) .. 단순하게 공백 문자만 봐도 종류가 한두개가 아니다.. 

하지만.. 결론은.. PG 도 다른 DBMS 와 마찮가지로 컬레이션을 설정할 수 있었다!!!! .(지금 너무 멀리와버렸는데... 큰일났다 진짜.... 진심.... )
(왜 메뉴얼대로는 안되는지 모르겠지만..이 부분은 더 확인을 해야한다..)

아래 테스트는 편의상 컬럼단위로 설정했지만.. database 또는 스키마 단위로 설정이 가능할것이다. 

 

먼저 메뉴얼에 있는 예시는 아래와 같은데.. 이건 내 환경에선 안된다... ..

 

CREATE COLLATION case_insensitive (
provider = icu,
locale = 'und-u-ks-level2',  
deterministic = false
);

 

그래서 다시 찾은게 있는데...

 

요기서 찾은대로 하니깐 된다... (https://dba.stackexchange.com/questions/255780/case-insensitive-collation-still-comparing-case-sensitive  왜 이런 중요한 내용이 메뉴얼엔 없을까??)

>> 하지만.. 메뉴얼대로 하는게 더 많은 것을 지원하는같다.. 더 찾아봐야 함.

CREATE COLLATION collat_ci2 ( provider = 'icu', locale = '@colStrength=Primary', deterministic = false ); UTS10-D4. Primary Weight: The first collation weight in a collation element. A primary weight is also called the Level 1 weight. Level 1 is also abbreviated as L1. UTS10-D5. Secondary Weight: The second collation weight in a collation element. A secondary weight is also called the Level 2 weight. Level 2 is also abbreviated as L2. UTS10-D6. Tertiary Weight: The third collation weight in a collation element. A tertiary weight is also called the Level 3 weight. Level 3 is also abbreviated as L3. UTS10-D7. Quaternary Weight: The fourth collation weight in a collation element. A quaternary weight is also called the Level 4 weight. Level 4 is also abbreviated as L4. Table 2. Comparison Levels   Level Description Examples L1 Base characters role < roles < rule L2 Accents role < rôle < roles L3 Case/Variants role < Role < rôle L4 Punctuation role < “role” < Role Ln Identical role < ro□le < “role”

레벨에 따라 설정것이 다른것 같은데... 일단 가장높은 단계로 설정하니 모든게 해결된다..

 

테스트 코드

CREATE COLLATION collat_ci2 ( provider = 'icu', locale = '@colStrength=Primary', deterministic = false );   CREATE TABLE names( first_name varchar(10) collate collat_ci2, last_name varchar(10) collate collat_ci2 ); insert into names values ('Anton','Eggeｒ'), ('Anton','EggeＲ'), ('Berta','egger'), ('Berta','egger'), ('Conrad','Egger'); insert into names values ('Anton','cote'), ('Anton','coté'), ('Anton','côte'), ('Conrad','côté');

 

조회결과

select * from names where last_name= 'egger'   first_name last_name Anton Egger Berta egger Conrad Egger Anton Eggeｒ Anton EggeＲ   Select * from names where last_name= 'cote' first_name last_name Anton cote Anton coté Anton côte Conrad côté

공백(whitespace) 대한 언급이 있지만, 뒷공백 무시에 대한 이야기는 아닌것 같음. 
   - 다른 dbms의 경우 varchar 타입일때 뒷공백이 제거되는데, pg 는 text 타입으로 생성되면서, 뒷공백이 무시되는건 아닌것 같음.. 하지만 더 찾아보면 될수도 ㅋㅋㅋ


정렬순서


pg 메뉴얼에 정렬순서도 바꿀수 있는 옵션이 있음. 예를들면.. 소문자가 대문자보다 먼저 정렬순서를 바꿀수 있다던지...
dbms 를 변경하고, 정렬순서가 달라진다는 민원을 해결할 수 있을까? ㅎㅎ

내용추가...

 

그런데 like 검색은 안된다?????? 아... PG놈들 ... 만들다 말았네...

https://stackoverflow.com/questions/71663281/postgresql-case-insensitive-and-accent-insensitive-search

이와 관련된 블로거 게시글

https://www.cybertec-postgresql.com/en/case-insensitive-pattern-matching-in-postgresql/