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

42P21
2026년 09월 10일 | DBMS Error 가이드

이 글에서 다루는 내용

42P21 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

42P21 collation mismatch 란?

PostgreSQL 에러 코드 42P21은 collation mismatch, 즉 정렬 규칙 불일치 오류입니다. 이 에러는 두 개 이상의 문자열 값 또는 컬럼을 비교하거나 결합할 때, 각각에 적용된 collation(정렬 규칙)이 서로 달라 PostgreSQL이 어떤 규칙을 따라야 할지 결정하지 못할 때 발생합니다. 예를 들어 UNION, JOIN, = 연산자, ORDER BY 등 다양한 SQL 구문에서 서로 다른 collation이 충돌하는 경우에 이 오류가 나타납니다.

주요 발생 원인

1. 서로 다른 collation이 지정된 컬럼 간 비교 또는 JOIN

가장 흔한 원인입니다. 테이블 A의 컬럼이 ko_KR.UTF-8 collation을 사용하고, 테이블 B의 컬럼이 en_US.UTF-8 collation을 사용하는 경우, 두 컬럼을 JOIN 조건이나 WHERE 절에서 비교하면 PostgreSQL은 어떤 collation 규칙을 적용해야 할지 알 수 없어 에러를 발생시킵니다. 이는 특히 여러 데이터베이스나 서로 다른 환경에서 덤프/복원한 데이터를 다룰 때 자주 발생합니다.

2. COLLATE 절이 명시적으로 충돌하는 쿼리

개발자가 쿼리 내에서 COLLATE 키워드를 직접 명시할 때, 동일한 표현식 안에 서로 다른 collation이 혼재하는 경우 에러가 발생합니다. 예를 들어 column1 COLLATE "C" = column2 COLLATE "ko_KR.UTF-8" 처럼 양쪽에 다른 collation을 지정하면 PostgreSQL은 우선순위를 결정할 수 없습니다. 이러한 실수는 동적 쿼리 생성이나 ORM 레이어에서 자동 생성된 쿼리에서 특히 많이 발생합니다.

3. UNION 또는 UNION ALL에서의 collation 불일치

UNION이나 UNION ALL로 두 개의 쿼리 결과를 합칠 때, 각 SELECT 절의 동일한 위치에 있는 컬럼의 collation이 서로 다르면 이 에러가 발생합니다. PostgreSQL은 UNION의 결과 타입을 결정하기 위해 두 컬럼의 collation을 통합해야 하는데, 서로 다른 collation이 지정되어 있으면 통합 규칙을 찾지 못합니다. 이 경우 일반적으로 한쪽 또는 양쪽에 명시적인 COLLATE 절을 추가하여 해결합니다.

해결 방법

원인 1 해결: JOIN 또는 비교 시 COLLATE 명시

한쪽 컬럼에 명시적으로 COLLATE 절을 추가하여 두 컬럼의 collation을 맞춰줍니다.

-- 에러 발생 예시
SELECT a.username, b.display_name
FROM users_kr a
JOIN users_en b ON a.username = b.display_name;
-- ERROR: 42P21: could not determine which collation to use for string comparison

-- 해결 방법: 한쪽 컬럼에 COLLATE 명시
SELECT a.username, b.display_name
FROM users_kr a
JOIN users_en b ON a.username = b.display_name COLLATE "en_US.UTF-8";

-- 또는 양쪽 모두 동일한 collation으로 맞추기
SELECT a.username, b.display_name
FROM users_kr a
JOIN users_en b 
  ON a.username COLLATE "C" = b.display_name COLLATE "C";

컬럼의 collation을 영구적으로 변경하고 싶다면 아래와 같이 ALTER TABLE을 사용합니다.

-- 컬럼 collation 영구 변경 (테이블 재작성 필요)
ALTER TABLE users_kr
  ALTER COLUMN username TYPE VARCHAR(100) COLLATE "en_US.UTF-8";

-- 변경 후 collation 확인
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'users_kr'
  AND column_name = 'username';

원인 2 해결: 쿼리 내 충돌하는 COLLATE 절 수정

동일한 비교 표현식 내에서 하나의 collation만 사용하도록 쿼리를 수정합니다.

-- 에러 발생 예시
SELECT *
FROM products
WHERE product_name COLLATE "C" = search_term COLLATE "ko_KR.UTF-8";
-- ERROR: 42P21: collation mismatch between explicit collations

-- 해결 방법: 하나의 collation으로 통일
SELECT *
FROM products
WHERE product_name COLLATE "C" = search_term COLLATE "C";

-- 또는 COLLATE를 전체 표현식 레벨에서 한 번만 사용
SELECT *
FROM products
WHERE (product_name = search_term) COLLATE "C";

원인 3 해결: UNION에서의 collation 불일치 해결

-- 에러 발생 예시
SELECT product_name FROM store_kr   -- collation: ko_KR.UTF-8
UNION ALL
SELECT product_name FROM store_en;  -- collation: en_US.UTF-8
-- ERROR: 42P21: UNION types could not be matched

-- 해결 방법: 양쪽 모두 동일한 collation으로 명시
SELECT product_name COLLATE "C" FROM store_kr
UNION ALL
SELECT product_name COLLATE "C" FROM store_en;

-- 또는 "default" collation 사용
SELECT product_name COLLATE "default" FROM store_kr
UNION ALL
SELECT product_name COLLATE "default" FROM store_en;

현재 데이터베이스 및 컬럼 collation 확인 방법

문제를 진단하기 위해 아래 쿼리를 활용하세요.

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

-- 특정 테이블의 모든 컬럼 collation 확인
SELECT
    c.table_name,
    c.column_name,
    c.data_type,
    c.collation_name
FROM information_schema.columns c
WHERE c.table_schema = 'public'
  AND c.table_name IN ('users_kr', 'users_en')
ORDER BY c.table_name, c.ordinal_position;

-- 시스템에서 사용 가능한 collation 목록 조회
SELECT collname, collencoding, collcollate, collctype
FROM pg_collation
ORDER BY collname;

예방 방법

1. 데이터베이스 설계 단계에서 collation 표준화

프로젝트 초기에 데이터베이스 전체의 collation 전략을 수립하고, 가능하면 모든 텍스트 컬럼에 동일한 collation을 사용하도록 정책을 수립해야 합니다. 특히 다국어 서비스를 제공하는 경우, ICU collation(PostgreSQL 10 이상) 또는 "C" collation을 기본으로 사용하고, 필요한 컬럼에만 특정 언어 collation을 적용하는 방식이 권장됩니다.

-- 데이터베이스 생성 시 collation 명시 (권장)
CREATE DATABASE myapp
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- ICU collation 사용 예시 (PostgreSQL 10+)
CREATE TABLE multilang_products (
    id SERIAL PRIMARY KEY,
    product_name TEXT COLLATE "und-x-icu",  -- 언어 중립적 ICU collation
    korean_name  TEXT COLLATE "ko-x-icu",
    english_name TEXT COLLATE "en-x-icu"
);

2. CI/CD 파이프라인에 collation 검증 단계 추가

마이그레이션이나 스키마 변경 시 자동으로 collation 불일치 여부를 검사하는 쿼리를 CI/CD 파이프라인에 포함시킵니다. 아래 쿼리를 정기적으로 실행하여 표준과 다른 collation을 가진 컬럼을 사전에 탐지할 수 있습니다.

-- 표준 collation('en_US.UTF-8')과 다른 collation을 가진 컬럼 탐지
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
  AND collation_name <> 'en_US.UTF-8'  -- 프로젝트 표준 collation
ORDER BY table_schema, table_name, column_name;