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

42P22 indeterminate collation 는?

PostgreSQL 에러 코드 42P22 indeterminate collation은 문자열 비교나 정렬 연산을 수행할 때 PostgreSQL이 어떤 collation(문자 정렬 규칙)을 사용해야 할지 결정하지 못할 때 발생합니다. 예를 들어 서로 다른 collation을 가진 두 컬럼이나 표현식을 =, <, > 등의 연산자로 비교하거나 ORDER BY, GROUP BY, LIKE 같은 구문에서 충돌이 생기면 이 에러가 발생합니다. 이는 데이터베이스가 문자열 비교 기준을 스스로 결정할 수 없기 때문에 사용자가 명시적으로 collation을 지정해 줘야 한다는 신호입니다.

---

주요 발생 원인

1. 서로 다른 collation을 가진 컬럼 간의 비교

이 에러가 발생하는 가장 흔한 원인입니다. 예를 들어 한 컬럼은 ko_KR.UTF-8 collation을, 다른 컬럼은 en_US.UTF-8 collation을 사용하고 있을 때, 두 컬럼을 직접 비교하면 PostgreSQL은 어떤 규칙을 기준으로 비교해야 할지 알 수 없습니다. 테이블을 다른 데이터베이스나 외부 시스템에서 마이그레이션할 때 자주 발생하는 상황입니다.

2. COLLATE 지시어가 없는 collation 미지정 표현식

사용자 정의 함수(UDF)나 복잡한 SQL 표현식 안에서 문자열을 반환할 때 명시적인 collation 정보가 없는 경우, PostgreSQL은 해당 표현식의 collation을 default로 간주할 수 없을 때 indeterminate로 처리합니다. 특히 CASE WHEN 구문이나 COALESCE, NULLIF 등의 조건 표현식에서 각 분기가 서로 다른 collation을 반환할 때 이 상황이 자주 발생합니다.

3. pg_catalog.default 와 사용자 지정 collation 혼용

데이터베이스 기본 collation(pg_catalog.default)과 사용자가 명시적으로 지정한 collation이 같은 표현식 안에서 혼용될 경우 충돌이 발생할 수 있습니다. 예를 들어 CREATE TABLE 시에 특정 컬럼에만 COLLATE "C"를 지정한 상황에서 다른 일반 텍스트 컬럼과 조인이나 비교를 수행하면 PostgreSQL이 적합한 collation을 결정하지 못하게 됩니다.

---

해결 방법

원인 1: 서로 다른 collation 컬럼 비교 시 명시적 COLLATE 지정

가장 직접적인 해결책은 비교 시 명시적으로 COLLATE 절을 사용하는 것입니다.

-- 에러 발생 예시
SELECT *
FROM users u
JOIN orders o ON u.username = o.customer_name;
-- ERROR: 42P22: could not determine which collation to use for string comparison

-- 해결: 명시적으로 collation 지정
SELECT *
FROM users u
JOIN orders o ON u.username COLLATE "ko_KR.UTF-8" = o.customer_name COLLATE "ko_KR.UTF-8";

-- 또는 한쪽에만 지정해도 됩니다
SELECT *
FROM users u
JOIN orders o ON u.username = o.customer_name COLLATE "ko_KR.UTF-8";

원인 2: 표현식 내 COLLATE 명시

CASE WHEN이나 COALESCE 등에서 발생하는 경우 각 반환 표현식에 동일한 collation을 지정합니다.

-- 에러 발생 예시
SELECT CASE
    WHEN active THEN username
    ELSE nickname
END AS display_name
FROM users
ORDER BY display_name;
-- ERROR: 42P22: indeterminate collation

-- 해결: 각 분기에 동일한 collation 지정
SELECT CASE
    WHEN active THEN username COLLATE "ko_KR.UTF-8"
    ELSE nickname COLLATE "ko_KR.UTF-8"
END AS display_name
FROM users
ORDER BY display_name;

-- COALESCE 예시
SELECT COALESCE(
    preferred_name COLLATE "ko_KR.UTF-8",
    username COLLATE "ko_KR.UTF-8"
) AS effective_name
FROM users;

원인 3: 테이블 설계 단계에서 collation 통일

근본적인 해결은 테이블 생성 시 collation을 통일하는 것입니다.

-- 문제가 있는 테이블 구조
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT COLLATE "en_US.UTF-8",
    nickname TEXT COLLATE "ko_KR.UTF-8"  -- 충돌 가능성!
);

-- 권장 구조: 동일한 collation 사용
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT COLLATE "ko_KR.UTF-8",
    nickname TEXT COLLATE "ko_KR.UTF-8"
);

-- 기존 컬럼의 collation 확인
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'users';

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

-- 컬럼 collation 변경 (데이터 재적재 필요)
ALTER TABLE users
ALTER COLUMN nickname TYPE TEXT COLLATE "ko_KR.UTF-8";

ORDER BY에서의 임시 해결

-- 정렬 시 collation 충돌 해결
SELECT id, username, nickname
FROM users
ORDER BY username COLLATE "ko_KR.UTF-8";

-- LIKE 검색 시 collation 지정
SELECT *
FROM users
WHERE username COLLATE "ko_KR.UTF-8" LIKE '김%';

---

예방 방법

1. 데이터베이스 생성 시 collation을 명확히 지정하고 전체적으로 통일

데이터베이스 생성 단계에서 collation을 명시적으로 지정하고, 이후 모든 텍스트 컬럼이 동일한 collation을 따르도록 팀 내 개발 표준을 수립해야 합니다. 특히 서로 다른 시스템 간 데이터를 마이그레이션하기 전에 반드시 collation 호환성을 점검하는 절차를 마련하세요.

-- 올바른 데이터베이스 생성 예시
CREATE DATABASE myapp
    WITH ENCODING 'UTF8'
    LC_COLLATE = 'ko_KR.UTF-8'
    LC_CTYPE = 'ko_KR.UTF-8'
    TEMPLATE = template0;

2. 코드 리뷰 및 테스트에서 collation 검증 자동화

CI/CD 파이프라인에 collation 불일치를 탐지하는 쿼리를 추가하고, 문자열 비교가 포함된 모든 SQL에 대해 collation 명시 여부를 코드 리뷰 체크리스트에 포함시키세요. 아래 쿼리를 활용해 정기적으로 collation 불일치 컬럼을 모니터링하면 사전에 문제를 예방할 수 있습니다.

-- 데이터베이스 내 모든 텍스트 컬럼의 collation 점검 쿼리
SELECT
    t.table_schema,
    t.table_name,
    c.column_name,
    c.data_type,
    c.collation_name,
    d.datcollate AS db_collation
FROM information_schema.columns c
JOIN information_schema.tables t
    ON c.table_name = t.table_name
    AND c.table_schema = t.table_schema
CROSS JOIN pg_database d
WHERE d.datname = current_database()
  AND c.data_type IN ('text', 'character varying', 'character')
  AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND c.collation_name IS DISTINCT FROM d.datcollate
ORDER BY t.table_schema, t.table_name, c.column_name;

---

관련 에러

• 42P21 (collation_mismatch): 두 표현식 간의 collation이 명확히 충돌하는 경우로, 42P22와 유사하지만 더 명확하게 어떤 collation이 충돌하는지를 나타냅니다.
• 22021 (character_not_in_repertoire): 지정된 collation이 해당 문자를 지원하지 않을 때 발생합니다.
• 42804 (datatype_mismatch): 서로 다른 데이터 타입 간의 비교에서 발생하며, 종종 collation 문제와 함께 나타납니다.

---

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