[PostgreSQL] 한글 정렬이 안될 때(collate)

● 문제

  - ORDER BY를 이용하여 정렬하려고 하는데, 한글 정렬이 이상함

  - 예시)

SELECT [컬럼명]
FROM [테이블명]
ORDER BY [컬럼명];

 

● 원인

  - 아래의 쿼리를 실행하면 시스템 카탈로그 테이블(PG_DATABASE)을 통해 각 데이터베이스마다 설정된 값을 확인 가능

 

SELECT DATNAME, DATDBA, ENCODING, DATCOLLATE, DATCTYPE FROM PG_DATABASE;

  - 위 결과와 같이, 데이터베이스 내 문자열 데이터 정렬 순서를 지정해주는 datcollate가 en_US.UTF8로 설정되어 있음

  - datacollate default가 en_US.UTF8

  - en_US.UTF8은 영어 기준이기 때문에, 한글 기준인 ko_KR.utf8로 변경해줘야 함

 

  ● 해결

    1. PG_DATABASE 테이블의 해당  데이터베이스 DATACOLLATE 값 UPDATE   

 

UPDATE PG_DATABASE SET DATCOLLATE = 'ko_KR.utf8', 
WHERE DATNAME='[데이터베이스명]';

      

      => 이 방법으로 해서 성공한다면 다행이지만, 나는 그래도 안 됐다..

 

    2. collation C 설정 

      - 이 방법은 데이터베이스를 이미 생성한 후에는 수정이 불가능함

      - 데이터베이스를 백업하고 다시 생성하여 사용할 수 있는 상황일 경우에만 추천

      - 데이터베이스 생성 시 Collation을 C로 설정

 

 

      - 아래와 같은 오류가 뜬다면, HINT에 적힌 것처럼 template0으로 template 생성 후 Tablespace를 template0으로 설정

 

      => 이 방법이 가능하다면 다행이지만, 나는 데이터베이스를 새로 만들 수 없는 상황이었다...

 

     3. 쿼리에서 바로 collate 옵션 사용

      - 임시방편의 느낌이 있지만, 알아두면 좋을 것 같다.

 

SELECT [컬럼명]
FROM [테이블명]
ORDER BY [컬럼명] COLLATE "ko_KR.utf8";

 

      => 위 방법으로 성공🎉

 

 

---

참고

• No.190 - postgresql 한글 order by의 기준
  https://uiandwe.tistory.com/1380
• 오누! - [Postgres] 한글 정렬이 제대로 되지 않은 경우
  https://onu0624.tistory.com/63 
• sungtae - [PostgreSQL] 한글 정렬 시 collate 옵션 사용하기
  https://sungtae-kim.tistory.com/68