References
Collation ?
- 텍스트 데이터의 정렬 방식을 결정하는 속성
- 기본 값은
en_US.UTF-8 이며, 한글 데이터에 기본 값으로 설정되어 있으면 의도치 않은 정렬 결과를 얻게 된다.
- 예:
'가나다', '라마' 이렇게 두 데이터를 오름차순으로 정렬하면 '가나다'가 앞에 와야 하지만, 글자 수가 더 적은 '라마' 가 앞에 오게 된다.
SELECT 수행 시 Collation 지정
SELECT *
FROM (VALUES
(1, 'aaa', '가가가'),
(2, 'bb', '나나'),
(3, 'ccccc', '다다다다'),
(10, 'e', '라'),
(11, 'f', '마마마마마마'),
(12, 'gggg', '바'),
(13, 'eeee', '사')
) AS T(num, en, ko)
ORDER BY ko
COLLATE "ko_KR.utf8" -- > 문자열 길이보다 사전순으로 우선하여 정렬된다.
-- COLLATE "en_US.utf8" -- > 문자열 길이가 우선되어, 의도치 않게 정렬된다.
---------------
-- 추가
---------------
ORDER BY ko COLLATE "ko_KR.utf8" ASC -- OK
ORDER BY ko COLLATE "ko_KR.utf8", num -- OK
ORDER BY (ko COLLATE "ko_KR.utf8", num) DESC -- OK
ORDER BY ko ASC COLLATE "ko_KR.utf8" -- 에러
ORDER BY ko, num COLLATE "ko_KR.utf8" -- 에러(문자열이 아닌 컬럼에 대한 Collation 설정 불가)
ORDER BY (ko, num) COLLATE "ko_KR.utf8" -- 에러(Record에 대한 Collation 설정 불가)
사용 가능한 Collation 목록 확인
SELECT * FROM pg_collation;
현재 적용된 Collation 확인
-- 각 DB에 설정된 Collation 확인(기본 en_US.UTF-8)
select datname, datdba, encoding, datcollate, datctype from pg_database;
-- 현재 DB에서 특정 테이블 내의 모든 컬럼의 Collation 확인
WITH defcoll AS (
SELECT datcollate AS coll
FROM pg_database
WHERE datname = current_database()
)
SELECT a.attname AS column_name,
CASE WHEN c.collname = 'default'
THEN defcoll.coll
ELSE c.collname
END AS collation
FROM pg_attribute AS a
CROSS JOIN defcoll
LEFT JOIN pg_collation AS c ON a.attcollation = c.oid
WHERE a.attrelid = '{스키마}.{테이블}'::regclass
AND a.attnum > 0
ORDER BY attnum;
Collation 변경
- DB의 Collation은 DB를 생성할 때부터 지정 하는 것이 좋다.
- Collation 변경 시, 데이터를 백업하고 하는 것이 안전하다.
-- DB Collation 변경
update pg_database set datcollate='ko_KR.utf8' where datname='{DB_이름}';
-- 테이블 컬럼의 Collation 변경
ALTER TABLE {DB}.{스키마}.{테이블}
ALTER COLUMN {컬럼명} TYPE {컬럼타입} COLLATE "ko_KR.utf8";
Create Database + Collation
[1] Collation ‘C’