[Postgresql/Greenplum] Order by Collate/NULLS 를 이용하여 데이터 정렬기준 변경하기

 

DBMS 마이그레이션을 프로젝트를 수행하면 꼭 나오는 유형이 정렬기준이 다르게 적용되는 점이다.

정렬기준이 다르게 적용되면 쿼리나 어플리케이션의 버그처럼 보여지면서 변경된 DBMS의 신뢰도가 확 낮아진다는 점이다. (그러면 예전 DB는 잘됐어요. Greenplum 또는 Postgresql 못 쓰겠네 라는 얘기를 백만번쯤 듣게 될 것이다.)

예를 들면

1. MySQL,SqlServer DBMS 에서  Greenplum 으로 DBMS를 변경하여 테이블의 데이터가 같아도 영문데이터가 먼저 나와야 하는데 한글 데이터가 먼저 표시되는 경우

2. Greenplum/Postgresql DBMS에서 order by 쿼리를 실행한 데이터와 엑셀의 정렬 기능을 사용한 데이터를 비교하는데 순서가 맞지 않아 정렬을 다시 해야하는 경우

3. 기존 DBMS에서 사용하던 쿼리를 실행하였는데 특정컬럼의 NULL 인 데이터가 제일 상단 또는 하단에 표시되어 데이터검증 자체가 안되는 경우

테스트환경

• Postgresql 13.1(CentOS 7.6)

정렬기준을 변경하려면 collate 명령어를 사용하면 된다.

-- collate 를 이용하여 title컬럼의 영문데이터가 먼저 나오게 정렬순서를 변경할 수 있다.
SELECT no, title, content, create_dt, update_dt
  FROM dummy_table
 ORDER BY title COLLATE "ko_KR.utf8" ASC -- 영문이 먼저, 한글이 그 다음으로 표시되게 정렬

collate 정렬순서변경

유의사항으로써 collate 값은 꼭 쌍따옴표를 이용해야 하며 대소문자를 정확히 구분해야 한다.

의문점은 "ko_KR.utf8" ASC 를 사용하면 한글이 먼저 정렬이 될 것 같으나 영문이 먼저 정렬이 된다.

 

그동안의 경험으로는 collate 설정값을 홑따옴표로 해야 할 것 같은데 홑따옴표로 사용하면 Syntax 에러가 발생한다.

SQL Error [42601]: ERROR: syntax error at or near "'ko_KR.utf8'"

 

대소문자를 정확히 구분하지 않거나 지원하지 않는 collate를 지정하면 SQL 42704 ERROR가 발생한다.

SQL Error [42704]: ERROR: collation "KO_KR.UTF8" for encoding "UTF8" does not exist

 

현재 DB의 collate 를 확인하는 방법은 SHOW lc_collate 명령어이며 collate 명령어없이 order by 를 사용하여 데이터를 정렬할때 해당 DB의 collcate 값을 기준으로 정렬이 된다.

 

당연히 ko_KR 만 지원하지는 않고 상당히 많은 collate를 지원하며 pg_collation 카탈로그 테이블을 조회하면 지원가능 collate를 확인할 수 있다.

SELECT *
  FROM pg_collation
 WHERE collname LIKE 'ko_%'
 ORDER BY collname

 

유의사항은 DB collate가 ko_KR.utf8 으로 설정되어 있으면 ko_KR.euckr 로 정렬기준을 변경할 수는 없는 것으로 파악이 된다.

 

권장하지 않지만 테이블 생성시 컬럼별로 collate를 지정할 수도 있다.

테이블의 컬럼별 collate 지정

-- 컬럼별로 collate 지정
CREATE TABLE public.test1 (
    a text COLLATE "ko_KR.utf8"
  , b text COLLATE "en_US.utf8"
);

 

NULL 컬럼값 정렬기준을 NULLS FIRST, NULLS LAST 명령어를 이용하여 변경할 수 있다.

title 컬럼을 오름차순(ASC)로 정렬하였을때 맨 밑에 표시되는 것을 확인할 수 있다.

 

이 상황에서 NULLS FIRST 명령어를 사용하면 NULL인 데이터를 제일 먼저 정렬되게 변경할 수 있다.

NULLS FIRST

 

NULL인 데이터를 마지막으로 정렬하고 싶으면 NULLS LAST 명령어를 사용하면 된다. (NULLS LAST는 order by 의 기본정렬 설정인것 같아 사용빈도는 낮을 것 같다.)

NULLS LAST