PostgreSQL 42P22 오류 원인과 해결 방법 완벽 가이드

42P22 indeterminate collation 는?

PostgreSQL 에러 코드 42P22는 “indeterminate collation”, 즉 불확정 콜레이션(정렬 규칙) 에러입니다. 이 에러는 문자열 비교, 정렬, 또는 특정 함수 실행 시 PostgreSQL이 어떤 콜레이션(collation) 규칙을 적용해야 할지 결정하지 못할 때 발생합니다. 주로 서로 다른 콜레이션을 가진 두 문자열 표현식을 비교하거나 결합할 때, 혹은 콜레이션이 명시되지 않은 채로 콜레이션 의존적인 함수를 호출할 때 나타납니다.

---

주요 발생 원인

1. 서로 다른 콜레이션을 가진 컬럼 간 비교 또는 결합

가장 흔한 원인입니다. 테이블 A의 컬럼이 ko_KR.UTF-8 콜레이션을, 테이블 B의 컬럼이 en_US.UTF-8 콜레이션을 가지고 있을 때, 두 컬럼을 직접 비교하거나 UNION, INTERSECT 등의 집합 연산을 수행하면 PostgreSQL은 어느 콜레이션을 기준으로 삼아야 할지 알 수 없습니다. 이 상황에서 PostgreSQL은 자동으로 규칙을 선택하지 않고 에러를 발생시킵니다.

2. COLLATE 절 없이 콜레이션 의존적인 함수 사용

upper(), lower(), initcap(), regexp_match() 등의 함수는 내부적으로 콜레이션에 의존합니다. 파라미터로 전달된 표현식이 콜레이션을 결정할 수 없는 상태(예: 서로 다른 콜레이션 문자열의 결합 결과 또는 UNKNOWN 타입)이면, 함수는 어떤 로케일 규칙으로 문자를 변환해야 할지 모르기 때문에 에러가 발생합니다. 특히 동적 SQL이나 복잡한 서브쿼리에서 자주 목격됩니다.

3. 명시적 COLLATE 없이 CASE 표현식이나 COALESCE에서 혼합 콜레이션 사용

CASE WHEN ... THEN col1 ELSE col2 END 또는 COALESCE(col1, col2) 형태에서 col1과 col2가 각각 다른 콜레이션을 가질 경우, PostgreSQL은 결과 표현식의 콜레이션을 하나로 결정할 수 없습니다. 이 경우 해당 표현식의 결과가 이후 정렬(ORDER BY)이나 비교 연산에 사용될 때 42P22 에러가 발생합니다.

---

해결 방법

해결 1: COLLATE 절을 명시적으로 지정하기

가장 직접적인 해결책입니다. 비교하는 컬럼이나 표현식에 동일한 콜레이션을 명시적으로 지정합니다.

-- 에러 발생 예시: 두 컬럼의 콜레이션이 다를 경우
SELECT *
FROM table_a a
JOIN table_b b ON a.name = b.name; -- 42P22 발생 가능

-- 해결: 특정 콜레이션으로 통일
SELECT *
FROM table_a a
JOIN table_b b ON a.name = b.name COLLATE "en_US.UTF-8";

-- 또는 양쪽 모두 명시
SELECT *
FROM table_a a
JOIN table_b b ON a.name COLLATE "en_US.UTF-8" = b.name COLLATE "en_US.UTF-8";

해결 2: COLLATE 절을 사용해 함수 호출 문제 해결하기

콜레이션 의존 함수에 전달하는 인자에 명시적으로 콜레이션을 지정합니다.

-- 에러 발생 예시
SELECT upper(col1 || col2) -- col1, col2의 콜레이션이 다를 경우 42P22 발생
FROM my_table;

-- 해결: 연결된 결과에 COLLATE 지정
SELECT upper((col1 || col2) COLLATE "en_US.UTF-8")
FROM my_table;

-- 또는 각 컬럼에 동일 콜레이션 적용 후 연결
SELECT upper(col1 COLLATE "en_US.UTF-8" || col2 COLLATE "en_US.UTF-8")
FROM my_table;

해결 3: CASE / COALESCE 내 혼합 콜레이션 해결하기

결과 표현식 또는 각 분기(branch)에 콜레이션을 명시합니다.

-- 에러 발생 예시
SELECT CASE
    WHEN condition THEN col_ko  -- ko_KR 콜레이션
    ELSE col_en                 -- en_US 콜레이션
END AS result_col
FROM my_table
ORDER BY result_col; -- 여기서 42P22 발생

-- 해결: 각 분기에 동일 콜레이션 적용
SELECT CASE
    WHEN condition THEN col_ko COLLATE "en_US.UTF-8"
    ELSE col_en COLLATE "en_US.UTF-8"
END AS result_col
FROM my_table
ORDER BY result_col;

-- COALESCE 해결 예시
SELECT COALESCE(col1 COLLATE "en_US.UTF-8", col2 COLLATE "en_US.UTF-8") AS merged
FROM my_table
ORDER BY merged;

해결 4: 컬럼의 콜레이션 자체를 통일하기 (근본 해결책)

장기적으로는 테이블 정의 단계에서 동일한 콜레이션을 사용하는 것이 가장 좋습니다.

-- 현재 컬럼 콜레이션 확인
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'my_table';

-- 컬럼 콜레이션 변경 (ALTER TABLE)
ALTER TABLE my_table
    ALTER COLUMN name TYPE VARCHAR(100) COLLATE "en_US.UTF-8";

-- 새 테이블 생성 시 콜레이션 통일
CREATE TABLE unified_table (
    id SERIAL PRIMARY KEY,
    name_ko VARCHAR(100) COLLATE "en_US.UTF-8",
    name_en VARCHAR(100) COLLATE "en_US.UTF-8"
);

-- 데이터베이스 기본 콜레이션 확인
SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

---

예방 방법

1. 데이터베이스 및 테이블 생성 시 콜레이션 표준화

프로젝트 초기부터 데이터베이스 전체에서 사용할 콜레이션을 하나로 표준화하고, 이를 개발 가이드라인 및 DDL 템플릿에 명시해야 합니다. CREATE DATABASE 시 LC_COLLATE를 지정하고, 특수한 목적이 아니라면 컬럼별로 다른 콜레이션을 지정하는 것을 원칙적으로 금지하는 정책을 수립하세요. 예를 들어, 팀 전체가 en_US.UTF-8 또는 C 콜레이션을 표준으로 채택하면, 교차 비교 시 발생하는 대부분의 42P22 에러를 사전에 차단할 수 있습니다.

-- 표준 콜레이션으로 데이터베이스 생성 예시
CREATE DATABASE myapp
    WITH ENCODING 'UTF8'
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE   = 'en_US.UTF-8'
         TEMPLATE   = template0;

2. 코드 리뷰 및 CI/CD 파이프라인에 콜레이션 검증 추가

다국어 문자열 컬럼이 포함된 쿼리를 코드 리뷰 시 반드시 콜레이션 충돌 가능성을 점검하고, 가능하다면 CI/CD 파이프라인에서 pg_attribute 및 information_schema.columns를 활용해 콜레이션 불일치 컬럼 간 조인이나 비교가 발생하는지 정적 분석 단계에서 탐지하는 스크립트를 작성하여 운영하세요.

-- 콜레이션이 혼재하는 컬럼 탐지 쿼리 (DBA 모니터링용)
SELECT
    table_schema,
    table_name,
    column_name,
    collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND collation_name IS NOT NULL
ORDER BY table_schema, table_name, column_name;

---

관련 에러

• 42P21 (collation_mismatch): 두 콜레이션이 명확하게 다르다는 것을 PostgreSQL이 알고 충돌을 보고하는 에러로, 42P22와 유사하지만 “불확정”이 아닌 “불일치”가 핵심 차이입니다.
• 42804 (datatype_mismatch): 데이터 타입 불일치 에러로, 콜레이션 문제와 함께 발생하는 경우가 많습니다. 특히 암묵적 형 변환이 포함된 복잡한 쿼리에서 동반 발생할 수 있습니다.
• 22021 (character_not_in_repertoire): 특정 인코딩에서 표현할 수 없는 문자가 포함된 경우로, 다국어 환경에서 콜레이션 문제와 함께 자주 나타나는 에러입니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.