요약: 애초에 인코딩은 꼭 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로 시작하는 것들로 정렬해보니 영어보다 한글이 먼저 나온다ㅠㅠ
'DB' 카테고리의 다른 글
| InfluxDB2 backup (0) | 2024.02.06 |
|---|