postgreSQL 한글정렬 문제

요약: 애초에 인코딩은 꼭 utf8로 설치하고 데이터베이스 생성시 collate는 "C.utf8"로 하자
백업복원으로 재생성할 수 없다면 일일히 수정해야 한다

ALTER TABLE [테이블명]
ALTER COLUMN [컬럼명]
SET DATA TYPE character varying(10) COLLATE "C.utf8";

테스트링크: https://onecompiler.com/postgresql/43pk5sws4

SHOW lc_collate;

SELECT * FROM pg_collation WHERE collname like 'ko%' or collname like '%utf%';

-- create
CREATE TABLE test_table (
  seq SERIAL PRIMARY KEY,
  cd VARCHAR(10) NOT NULL,
  nm VARCHAR(10)  NOT NULL,
  nm2 VARCHAR(10)  NOT NULL COLLATE "C.utf8"
);

-- insert
INSERT INTO test_table (cd, nm, nm2) VALUES ('BROWN', '브라운', '브라운');
INSERT INTO test_table (cd, nm, nm2) VALUES ('FUBAO', '푸바오', '푸바오');
INSERT INTO test_table (cd, nm, nm2) VALUES ('ETC', 'ETC', 'ETC');
INSERT INTO test_table (cd, nm, nm2) VALUES ('NO', '123', '123');

-- fetch 
SELECT a.* FROM test_table as a ORDER BY nm;
SELECT a.* FROM test_table as a ORDER BY nm2;

 

 

이하는 삽질로 얻은 지식 정리

1. 데이터베이스 스키마의 locale 확인

SHOW lc_collate

- 이것은 변경이 불가능하다. 데이터베이스 스키마를 새로 생성해야한다. 그리고 이것으로 기본 정렬된다.

- SELECT * FROM pg_database 에서는 모든 데이터베이스 스키마의 정보를 확인할 수 있고 UPDATE pg_database  SET  어쩌고 해서 수정할 수도 있으나 정렬 자체는 변하지 않았다.

 

2. 해당 스키마에서 사용할 수 있는 collate 확인

SELECT * FROM pg_collation 

한글 윈도우에서 기본 로케일로 설치한 것(이하 윈도우)은 utf8 관련은 전혀 없으나
docker로 설치한 것(이하 도커)은 collname기준 "C.utf8", "en_US.utf8" 두가지 있었다

=> 결론. 윈도우라도 인코딩 utf8로 설치해놔야 배포서버와의 환경차이로 인한 혼란을 줄일 수 있다.

 

3. 2의 테이블에 없는 것은 추가할 수 있다.

https://postgresql.kr/docs/13/sql-createcollation.html

ex) CREATE COLLATION [collation이름] (provider = icu, locale = 'ko_KR.utf8')

 

4. 데이터베이스의 collate를 바꾸지 않고 쿼리문에서 일시적으로 collate를 적용해서 정렬하는 방법

SELECT * FROM [테이블명] ORDER BY [컬럼명] COLLATE "ko-KR-x-icu"

collate 이름은 반드시 쌍따옴표로 해야 하며, 3의 테이블에서 collname컬럼을 사용해야 한다.
그런데 ko로 시작하는 것들로 정렬해보니 영어보다 한글이 먼저 나온다ㅠㅠ