PostgreSQL에서 한글 ORDER BY 정렬 문제 해결하기 COLLATE

데이터베이스를 사용할 때 정렬은 매우 중요한 작업입니다. 특히 한글 데이터를 다루는 경우, PostgreSQL에서 ORDER BY 절을 사용할 때 한글 정렬이 예상과 다르게 동작하는 문제를 경험할 수 있습니다. 이 글에서는 이러한 한글 정렬 문제를 해결하는 방법을 알아보겠습니다.

1. 문제 상황

PostgreSQL에서 한글 데이터를 ORDER BY로 정렬하면 한글이 올바르게 정렬되지 않는 경우가 있습니다. 예를 들어, 다음과 같은 데이터를 생각해봅시다.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (name) VALUES
('김영희'),
('이민수'),
('박철수'),
('최지수');

 

위 데이터를 name 기준으로 정렬하려고 할 때, 다음 쿼리를 사용합니다.

SELECT * FROM employees ORDER BY name;

 

하지만 결과가 의도한 대로 나오지 않고, 알파벳과 숫자가 섞이거나 한글 자음 순서가 올바르게 정렬되지 않을 수 있습니다.

2. 원인: 로케일(Locale) 설정 문제

PostgreSQL은 기본적으로 서버의 로케일 설정을 따릅니다. 로케일은 문자열을 정렬하는 방식을 결정하며, 한글 정렬을 위해서는 적절한 로케일 설정이 필요합니다. 한글 정렬이 제대로 되지 않는 이유는 PostgreSQL이 기본적으로 UTF-8 혹은 C 로케일을 사용하기 때문입니다. 이 경우, 한글이 알파벳이나 숫자와 같은 방식으로 정렬되어 예상과 다른 결과를 초래할 수 있습니다.

3. 해결 방법: 로케일 설정 변경

3.1 데이터베이스 생성 시 로케일 지정

PostgreSQL에서는 데이터베이스를 생성할 때 로케일을 지정할 수 있습니다. 한글 정렬을 위해서는 ko_KR.UTF-8로케일을 사용해야 합니다.

다음 명령어로 새로운 데이터베이스를 생성할 때, 로케일을 지정합니다.

CREATE DATABASE mydb WITH OWNER myuser
    LC_COLLATE='ko_KR.UTF-8'
    LC_CTYPE='ko_KR.UTF-8'
    ENCODING='UTF8'
    TEMPLATE=template0;

 

이렇게 생성한 데이터베이스에서는 한글 정렬이 제대로 동작합니다.

3.2 기존 데이터베이스에서 로케일 변경

이미 생성된 데이터베이스의 로케일을 변경하는 것은 쉽지 않으므로, 새로운 데이터베이스를 생성하고 데이터를 옮기는 것이 일반적인 방법입니다. 기존 데이터베이스를 백업한 후 새로운 로케일로 생성한 데이터베이스에 데이터를 복원하는 절차는 다음과 같습니다.

1. 기존 데이터베이스 백업:

pg_dump mydb > mydb_backup.sql

 

2.  새로운 데이터베이스 생성:

CREATE DATABASE mydb_new WITH OWNER myuser
    LC_COLLATE='ko_KR.UTF-8'
    LC_CTYPE='ko_KR.UTF-8'
    ENCODING='UTF8'
    TEMPLATE=template0;

 

3.   백업 파일을 새로운 데이터베이스로 복원:

psql mydb_new < mydb_backup.sql

 

3.3 COLLATE를 이용한 정렬

만약 데이터베이스 로케일을 변경할 수 없는 상황이라면, 쿼리에서 COLLATE 절을 이용해 한글 정렬을 강제로 지정할 수 있습니다.

SELECT * FROM employees ORDER BY name COLLATE "ko_KR.UTF-8";

 

이렇게 하면 데이터베이스의 기본 로케일에 상관없이 한글 정렬이 제대로 이루어집니다.

4. 예제

아래는 한글 정렬이 올바르게 이루어지는 예제입니다.

-- 한글 데이터를 포함한 테이블 생성
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- 데이터 삽입
INSERT INTO employees (name) VALUES
('김영희'),
('이민수'),
('박철수'),
('최지수');

-- 한글 정렬 쿼리 실행
SELECT * FROM employees ORDER BY name COLLATE "ko_KR.UTF-8";

 

PostgreSQL에서 한글 정렬이 제대로 되지 않는 문제는 로케일 설정으로 인해 발생하는 경우가 많습니다. 데이터베이스 생성 시 적절한 로케일을 지정하거나, COLLATE 절을 사용해 문제를 해결할 수 있습니다. 한글 데이터를 다룰 때는 항상 로케일 설정을 확인하여 올바른 정렬이 이루어지도록 하는 것이 중요합니다.

이 방법을 적용하면 한글 정렬 문제를 쉽게 해결할 수 있을 것입니다.