[트러블슈팅] PostgreSQL에서 한국어 정렬(ORDER BY) 문제 해결 방법 - Collation

[문제 상황]

조회 기능을 만드는데 order by로 정렬을 하는데 한국어가 정렬이 안된다.

 

데이터베이스의 로케일(Locale) 설정과 정렬 방식(Collation) 때문인데, PostgreSQL은 데이터베이스를 생성할 때 locale 지정을 해줘야하고, 만들어진 DB에는 수정이 불가하다고 한다...

 

따라서 기존의 테이블에서 정렬하는 해결 방법을 알아보자.

 

1. 현재 데이터베이스의 로케일(Locale) 확인하기

PostgreSQL의 정렬 방식은 데이터베이스가 생성될 때 결정된다.

현재 사용 중인 데이터베이스의 로케일을 먼저 확인하자.

 

PostgreSQL 실행해서 다음 명령어를 쳐주면 된다.

---

* PostgreSQL 실행하는 법

PostgreSQL 설치 및 실행(homebrew, Macbook M2 Pro)

---

SELECT datname, datcollate, datctype FROM pg_database WHERE datname = 'your_database_name';

출력 결과에서 datcollate 값이 ko_KR.UTF-8이 아니라면, 한국어 정렬이 정상적으로 동작하지 않을 가능성이 높다.

 

나는 확인해보니 다음과 같이 나온다.

 

2. 한국어 정렬을 위한 새로운 데이터베이스 생성

일단 생성할 때 Locale 지정하는 법을 기록해둬야겠다.

- 데이터베이스 생성 시 설정

CREATE DATABASE your_new_database
    LC_COLLATE = 'ko_KR.UTF-8'
    LC_CTYPE = 'ko_KR.UTF-8'
    TEMPLATE = template0;

• lc_collate: 정렬 순서에 영향을 미치는 설정.
• lc_ctype: 문자열의 대소문자 처리 및 문자 분류에 영향을 미치는 설정.
• TEMPLATE = template0: Locale을 변경하려면 반드시 template0을 사용해야 함.

LC_COLLATE와 LC_CTYPE을 한글 정렬을 지원하는 값으로 설정한다.

 

주의: LC_COLLATE와 LC_CTYPE은 데이터베이스 생성 후 변경할 수 없음.

   + 'ko_KR.UTF-8' 대신 'ko-KR-x-icu' 사용

       : 이유는 3번에서 나옵니다.

 

3. 특정 컬럼 정렬 시 강제 Collation 적용하기

만약 데이터베이스를 새로 생성할 수 없는 경우, ORDER BY에서 직접 Collation을 지정하여 해결할 수 있다.

SELECT * FROM your_table ORDER BY your_column COLLATE "ko_KR.UTF-8";

 

테스트로 은행 테이블을 만들어서 은행명을 기준으로 정렬하고 싶다.

기존에는 다음과 같이 정렬되었다.

select b.bank as name
from bank b
order by b.bank;

 

collation을 적용해서 데이터 조회를 해보자.

select b.bank as name
from bank b
order by b.bank collate "ko_KR.UTF-8";

 

??? 

서칭해서 하라는데로 하고있는데, ko_KR.UTF-8이 존재하지 않는다고 한다...

왜?? 

공식문서에서 찾아봤다. 

https://www.postgresql.org/docs/16/collation.html

24.2. Collation Support

24.2.2.2. Predefined Collations 
If the operating system provides support for using multiple locales within a single program (newlocale and related functions), or if support for ICU is configured, then when a database cluster is initialized, initdb populates the system catalog pg_collation with collations based on all the locales it finds in the operating system at the time.
To inspect the currently available locales, use the query SELECT * FROM pg_collation, or the command \dOS+ in psql.

24.2.2.2. 미리 정의된 데이터 정렬
운영 체제가 단일 프로그램(newlocale 및 관련 함수) 내에서 여러 로캘을 사용하도록 지원하거나 ICU에 대한 지원이 구성된 경우 데이터베이스 클러스터가 초기화되면 initdb는 운영 체제에서 발견한 모든 로캘을 기반으로 시스템 카탈로그 pg_collation을 데이터 정렬로 채웁니다.
현재 사용 가능한 로캘을 검사하려면 SELECT * FROM pg_collation 쿼리나 psql의 명령 \dOS+를 사용합니다.

 

> 해당 데이터베이스가 지원하는 미리 정의된 collate 이름을 사용하라고 한다.

 

 

그래서 먼저, PostgreSQL에서 사용 가능한 정렬 방식(COLLATE)을 확인해야 한다.

다음 쿼리를 실행하여 현재 데이터베이스에서 지원하는 COLLATE 목록을 확인하자.

찾기 쉽게 'ko'가 포함된 COLLATE만 검색하도록 해보자.

SELECT * FROM pg_collation WHERE collname ILIKE '%ko%';

 

현재 pg_collation 테이블을 확인한 결과, ko_KR.UTF-8 같은 일반적인 collate 옵션이 없고, ICU 기반(ko-KR-x-icu) 또는 glibc 기반(ko-KP) 옵션만 있다.
PostgreSQL에서는 ICU 기반 정렬을 지원하기 때문에 가장 적절한 "ko-KR-x-icu"를 사용한다.

 

따라서, 다시 sql문을 작성하여 정렬을 해보면

select b.bank as name
from bank b
order by b.bank collate "ko-KR-x-icu";

이렇게 잘 정렬되어 있는것을 볼 수 있다.

 

이 방법은 특정 컬럼에 대해서만 한국어 정렬을 적용하는 방식이다.

 

4. PostgreSQL 12 이상에서 ICU Collation 활용하기

PostgreSQL 12 이상에서는 ICU 기반 정렬을 사용할 수 있다.

이를 활용하면 더욱 정교한 한국어 정렬이 가능하다.

 

우선 나의 postgreSQL 버전을 확인하자. (SQL 쿼리 실행)

select version();

 

나는 16버전이니까, ICU Collation 사용이 가능하다.

CREATE COLLATION ko_kr (provider = icu, locale = 'ko-KR');
SELECT * FROM your_table ORDER BY your_column COLLATE ko_kr;

 

collation을 만들어서 이렇게 SQL문에 사용하면 된다.

 

5. 결론

그래서 데이터베이스를 새로 만드는 것이 힘든상황이라면? 

 

ICU 정렬은 다양한 언어를 지원하고, 보다 정확한 정렬을 제공하므로 PostgreSQL 12 이상을 사용한다면 활용하면 좋을 것 같다.