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

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

이 글에서 다루는 내용

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

42P21 collation mismatch 는?

PostgreSQL 에러 코드 42P21은 collation mismatch(콜레이션 불일치) 오류로, 두 개 이상의 문자열 표현식을 비교하거나 결합할 때 서로 다른 콜레이션(문자 정렬 규칙)이 적용되어 PostgreSQL이 어느 규칙을 따라야 할지 판단하지 못할 때 발생합니다. 콜레이션은 문자열의 정렬 순서, 대소문자 비교, 특수문자 처리 방식을 정의하는 규칙 집합으로, 데이터베이스, 테이블, 컬럼, 또는 쿼리 수준에서 각각 다르게 설정할 수 있습니다. 특히 여러 데이터베이스나 외부 시스템과 연동할 때, 또는 마이그레이션 과정에서 콜레이션이 혼재되는 상황이 자주 발생하며 이 에러가 트리거됩니다.

주요 발생 원인

서로 다른 콜레이션이 지정된 컬럼 간 비교 또는 조인

가장 흔한 원인입니다. 두 테이블의 컬럼이 각기 다른 콜레이션(en_US.UTF-8, ko_KR.UTF-8 등)으로 생성된 경우, 해당 컬럼을 JOIN, WHERE, ORDER BY 등에서 함께 사용하면 PostgreSQL은 어느 콜레이션 기준으로 비교해야 할지 결정하지 못해 오류를 발생시킵니다. 예를 들어, 사용자 테이블과 로그 테이블이 서로 다른 콜레이션으로 정의된 username 컬럼을 가지고 있을 때 조인하면 이 에러가 발생합니다.

COLLATE 절을 명시한 리터럴과 컬럼 간의 충돌

쿼리 내에서 COLLATE 절을 사용해 특정 콜레이션을 명시적으로 지정했을 때, 그 값이 비교 대상 컬럼의 콜레이션과 다르면 충돌이 발생합니다. 개발자가 특정 정렬 방식을 강제하려고 COLLATE "C"를 붙인 표현식과, 테이블에서 COLLATE "ko_KR.UTF-8"로 정의된 컬럼을 함께 사용하는 경우가 대표적입니다. 이런 패턴은 동적 쿼리나 ORM에서 자동 생성된 SQL에서 특히 자주 나타납니다.

데이터베이스 생성 시 콜레이션 설정 혼재 및 pg_dump/pg_restore 과정의 불일치

서로 다른 OS 환경(Linux vs macOS vs Windows)에서 PostgreSQL 데이터베이스를 생성하면 기본 콜레이션이 달라질 수 있습니다. 이 상태에서 pg_dump로 백업한 데이터를 다른 환경의 DB에 pg_restore하면, 원본 콜레이션을 그대로 재현하지 못하는 경우가 생깁니다. 또한 CREATE DATABASE시 LC_COLLATE, LC_CTYPE을 명시하지 않으면 OS의 로케일 설정을 그대로 따르기 때문에, 운영/개발 환경 간 차이가 운영 환경에서만 에러를 유발하는 원인이 됩니다.

해결 방법

원인 1: 서로 다른 콜레이션 컬럼 간 비교

문제가 되는 컬럼이나 표현식 중 하나에 COLLATE 절을 명시해 기준을 통일합니다.

-- 문제 발생 예시
SELECT a.username, b.log_message
FROM users a
JOIN activity_log b ON a.username = b.username;
-- ERROR: 42P21: could not determine which collation to use for string hashing

-- 해결 방법: COLLATE를 명시해 기준 콜레이션 통일
SELECT a.username, b.log_message
FROM users a
JOIN activity_log b ON a.username = b.username COLLATE "en_US.UTF-8";

-- 또는 양쪽 모두에 동일한 COLLATE 적용
SELECT a.username, b.log_message
FROM users a
JOIN activity_log b 
  ON (a.username COLLATE "C") = (b.username COLLATE "C");

컬럼 자체의 콜레이션을 변경하려면 ALTER TABLE을 사용합니다.

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

-- 컬럼 콜레이션 변경 (테이블 재작성 발생 - 운영 환경 주의)
ALTER TABLE users
  ALTER COLUMN username TYPE VARCHAR(100) COLLATE "en_US.UTF-8";

ALTER TABLE activity_log
  ALTER COLUMN username TYPE VARCHAR(100) COLLATE "en_US.UTF-8";

원인 2: 쿼리 내 COLLATE 절 충돌

명시적 COLLATE 절이 컬럼 정의와 충돌하는 경우, 쿼리에서 COLLATE "default"를 사용해 컬럼의 기본 콜레이션을 따르도록 하거나, 양쪽을 동일한 콜레이션으로 캐스팅합니다.

-- 문제 발생 예시
SELECT * FROM products
WHERE product_name = '노트북' COLLATE "C";
-- ERROR: 42P21: collation mismatch between implicit collations

-- 해결 방법 1: COLLATE "default" 사용
SELECT * FROM products
WHERE product_name = '노트북' COLLATE "default";

-- 해결 방법 2: 컬럼에도 동일 COLLATE 명시
SELECT * FROM products
WHERE product_name COLLATE "C" = '노트북' COLLATE "C";

-- 해결 방법 3: pg_collation을 활용한 콜레이션 존재 여부 확인
SELECT collname, collprovider, collencoding
FROM pg_collation
WHERE collname LIKE '%UTF%'
ORDER BY collname;

원인 3: 마이그레이션/복원 후 콜레이션 불일치

데이터베이스 레벨의 콜레이션 설정을 확인하고, 필요 시 올바른 콜레이션으로 새 DB를 생성한 후 데이터를 이전합니다.

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

-- 올바른 콜레이션으로 새 데이터베이스 생성
CREATE DATABASE myapp_production
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       TEMPLATE = template0;

-- 특정 스키마 내 모든 텍스트 컬럼의 콜레이션 일괄 확인
SELECT
    t.table_schema,
    t.table_name,
    c.column_name,
    c.data_type,
    c.collation_name
FROM information_schema.tables t
JOIN information_schema.columns c
  ON t.table_name = c.table_name
 AND t.table_schema = c.table_schema
WHERE t.table_schema = 'public'
  AND c.data_type IN ('character varying', 'text', 'character')
ORDER BY t.table_name, c.column_name;

예방 방법

데이터베이스 생성 시 콜레이션을 명시적으로 표준화하고, DDL 스크립트에서 컬럼 콜레이션을 일관되게 관리하라

모든 환경(개발, 스테이징, 운영)에서 동일한 콜레이션을 사용하도록 CREATE DATABASE 스크립트에 LC_COLLATE, LC_CTYPE, ENCODING을 항상 명시하십시오. 또한 팀 내 DDL 컨벤션으로 문자열 컬럼 생성 시 COLLATE 절을 명시하는 규칙을 수립하고, 이를 코드 리뷰 체크리스트에 포함시키는 것이 좋습니다. Flyway, Liquibase 같은 마이그레이션 도구를 사용할 경우, 마이그레이션 스크립트의 검증 단계에서 콜레이션 일관성을 자동으로 체크하는 스크립트를 추가하면 사전에 문제를 예방할 수 있습니다.

ICU 콜레이션 공급자를 활용해 OS 로케일 의존성을 제거하라

PostgreSQL 10 이상에서는 ICU(International Components for Unicode) 라이브러리 기반의 콜레이션을 지원합니다. ICU 콜레이션은 OS의 로케일 설정에 독립적으로 동작하므로, 플랫폼 간 이식성이 크게 향상됩니다. CREATE COLLATION 구문으로 프로젝트 전용 콜레이션을 정의하고 이를 일관되게 사용하면, 서로 다른 서버 환경에서도 동일한 동작을 보장할 수 있습니다.

“`sql

— ICU 기반 커스텀 콜레이션 생성 (PostgreSQL 10+)

CREATE COLLATION myapp_collation (

PROVIDER = icu,

LOCALE = ‘ko-u-ks-level2’ — 한국어, 대소문자 무시

);

— 생성된 콜레이션 적용

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

product_name TEXT COLLATE myapp_collation

);

“`