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

22005
2026년 10월 13일 | DBMS Error 가이드

이 글에서 다루는 내용

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

22005 error in assignment 는?

PostgreSQL 에러 코드 22005 (error in assignment)는 데이터를 특정 컬럼이나 변수에 할당(assignment)하는 과정에서 데이터 타입이 호환되지 않거나 변환이 불가능할 때 발생하는 에러입니다. 주로 INSERT, UPDATE 문 또는 PL/pgSQL 함수 내부에서 잘못된 타입의 값을 대입하려고 할 때 트리거됩니다. 이 에러는 SQL 표준 에러 클래스 22(데이터 예외)에 속하며, 런타임 시점에 발견되기 때문에 운영 환경에서 예기치 않게 나타날 수 있어 주의가 필요합니다.

주요 발생 원인

1. 호환되지 않는 데이터 타입 간 직접 할당

가장 흔한 원인은 서로 호환되지 않는 타입 간에 명시적 캐스팅 없이 값을 할당하려는 경우입니다. 예를 들어, INTEGER 타입 컬럼에 문자열 'abc'를 삽입하거나, DATE 컬럼에 올바르지 않은 형식의 날짜 문자열을 넣으려 할 때 PostgreSQL은 내부적으로 암묵적 캐스트를 시도하지만, 변환 규칙이 존재하지 않으면 22005 에러를 발생시킵니다. 특히 외부 시스템(ERP, API 등)에서 유입된 데이터가 타입 검증 없이 그대로 쿼리에 바인딩될 때 자주 발생합니다.

2. PL/pgSQL 함수 내 변수 타입 불일치

PL/pgSQL 블록에서 함수의 반환 타입이나 변수 선언 타입과 실제 할당되는 값의 타입이 다를 때 이 에러가 발생합니다. 예를 들어, RETURNS INTEGER로 선언된 함수가 내부적으로 NUMERIC 값을 반환 변수에 할당하거나, DECLARE 섹션에서 선언된 변수에 전혀 다른 타입의 쿼리 결과를 SELECT INTO로 받을 때 문제가 생깁니다. 이런 경우는 컴파일 타임이 아닌 런타임에서만 감지되므로 테스트 커버리지가 낮으면 운영 환경에서 처음 발견되는 경우가 많습니다.

3. 복합 타입(Composite Type) 또는 도메인(Domain) 제약 위반

사용자 정의 도메인(DOMAIN)이나 복합 타입에 값을 할당할 때, 해당 도메인의 CHECK 제약이나 타입 요구사항을 만족하지 못하면 22005 에러가 발생할 수 있습니다. 예를 들어 DOMAIN positive_int AS INTEGER CHECK (VALUE > 0)으로 정의된 도메인 타입 컬럼에 음수나 NULL을 삽입하려 할 때 발생합니다. 이 경우는 에러 메시지만으로는 어느 도메인, 어느 제약이 위반되었는지 파악하기 어려워 디버깅이 복잡해지는 경향이 있습니다.

해결 방법

원인 1 해결: 명시적 타입 캐스팅 사용

호환되지 않는 타입 간의 할당은 명시적 캐스트(:: 또는 CAST())를 사용하여 해결합니다.

-- 문제가 되는 쿼리 예시
INSERT INTO orders (quantity) VALUES ('10abc');  -- 22005 에러 발생

-- 해결 방법 1: 올바른 값 사용
INSERT INTO orders (quantity) VALUES (10);

-- 해결 방법 2: 명시적 캐스팅 (변환 가능한 경우)
INSERT INTO orders (quantity) VALUES (CAST('10' AS INTEGER));

-- 또는 PostgreSQL 단축 캐스트 문법
INSERT INTO orders (quantity) VALUES ('10'::INTEGER);

-- 날짜 타입 예시
INSERT INTO events (event_date) VALUES ('2024-13-45');  -- 잘못된 날짜, 22005 에러

-- 올바른 날짜 형식으로 수정
INSERT INTO events (event_date) VALUES ('2024-12-25'::DATE);

-- 입력값 유효성을 먼저 검증하는 방법
SELECT CASE 
    WHEN '2024-12-25' ~ '^\d{4}-\d{2}-\d{2}$' 
    THEN '2024-12-25'::DATE 
    ELSE NULL 
END AS safe_date;

원인 2 해결: PL/pgSQL 변수 타입 정렬

함수 내 변수 선언과 할당 타입을 일치시키거나, %TYPE 속성을 활용합니다.

-- 문제가 되는 PL/pgSQL 함수 예시
CREATE OR REPLACE FUNCTION get_total_price(order_id INT)
RETURNS INTEGER AS $$
DECLARE
    v_total INTEGER;  -- INTEGER로 선언했지만
BEGIN
    SELECT total_amount  -- NUMERIC 타입 컬럼
    INTO v_total         -- 여기서 22005 에러 발생 가능
    FROM orders
    WHERE id = order_id;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 해결 방법 1: 변수 타입을 컬럼 타입에 맞춤
CREATE OR REPLACE FUNCTION get_total_price(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_total NUMERIC;  -- 컬럼 타입과 일치
BEGIN
    SELECT total_amount
    INTO v_total
    FROM orders
    WHERE id = order_id;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 해결 방법 2: %TYPE 사용으로 자동 타입 추론 (권장)
CREATE OR REPLACE FUNCTION get_total_price(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_total orders.total_amount%TYPE;  -- 컬럼 타입 자동 상속
BEGIN
    SELECT total_amount
    INTO v_total
    FROM orders
    WHERE id = order_id;
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 반환 타입 불일치 해결: 명시적 캐스트 추가
CREATE OR REPLACE FUNCTION get_total_as_int(order_id INT)
RETURNS INTEGER AS $$
DECLARE
    v_total orders.total_amount%TYPE;
BEGIN
    SELECT total_amount INTO v_total
    FROM orders WHERE id = order_id;
    
    RETURN v_total::INTEGER;  -- 명시적 변환으로 안전하게 처리
END;
$$ LANGUAGE plpgsql;

원인 3 해결: 도메인 제약 확인 및 데이터 정제

도메인 정의를 확인하고, 삽입 전 데이터를 검증합니다.

-- 도메인 정의 확인
SELECT typname, typtype, typbasetype::regtype, typnotnull
FROM pg_type
WHERE typtype = 'd';

-- 또는 information_schema 사용
SELECT domain_name, data_type, constraint_name, check_clause
FROM information_schema.domain_constraints
JOIN information_schema.domains USING (domain_name);

-- 문제 예시: positive_int 도메인
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);

-- 22005 에러 발생 쿼리
INSERT INTO products (stock_count) VALUES (-5);  -- 도메인 위반

-- 해결: 삽입 전 유효성 검증 로직 추가
DO $$
DECLARE
    v_stock INTEGER := -5;
BEGIN
    IF v_stock <= 0 THEN
        RAISE EXCEPTION '재고 수량은 양수여야 합니다. 입력값: %', v_stock;
    END IF;
    
    INSERT INTO products (stock_count) VALUES (v_stock);
END;
$$;

-- 배치 작업 시 유효하지 않은 데이터 필터링 후 삽입
INSERT INTO products (stock_count)
SELECT quantity
FROM staging_products
WHERE quantity > 0;  -- 도메인 조건 사전 필터링

예방 방법

1. 애플리케이션 레이어에서 입력 데이터 타입 검증 강화

데이터베이스에 값을 전달하기 전에 애플리케이션 또는 ETL 파이프라인 단계에서 타입 유효성을 반드시 검사해야 합니다. ORM을 사용한다면 모델 레벨의 타입 어노테이션 및 유효성 검사기를 활용하고, 순수 SQL을 사용하는 경우 Prepared Statement와 파라미터 바인딩을 통해 타입 안전성을 확보하세요. 또한 데이터 파이프라인에서 pg_input_is_valid() 함수(PostgreSQL 16+)를 활용하면 캐스트 가능 여부를 사전에 확인할 수 있습니다.

-- PostgreSQL 16+ pg_input_is_valid() 활용 예시
SELECT pg_input_is_valid('abc', 'integer');   -- false 반환
SELECT pg_input_is_valid('123', 'integer');   -- true 반환
SELECT pg_input_is_valid('2024-12-25', 'date'); -- true 반환

-- 배치 처리 전 유효성 검사
SELECT *
FROM staging_data
WHERE NOT pg_input_is_valid(raw_value, 'integer');  -- 문제 레코드 식별

2. PL/pgSQL 함수 개발 시 %TYPE 및 %ROWTYPE 적극 활용

함수 내 변수를 선언할 때 하드코딩된 타입 대신 %TYPE과 %ROWTYPE을 사용하면 테이블 스키마 변경에도 자동으로 타입이 동기화되어 22005 에러를 예방할 수 있습니다. 또한 새 함수를 배포하기 전에 통합 테스트 환경에서 경계값(NULL, 최대값, 타입 경계값 등)을 포함한 단위 테스트를 반드시 수행하여 런타임 타입 에러를 사전에 차단하세요.

-- 권장 패턴: %TYPE과 %ROWTYPE 사용
CREATE OR REPLACE FUNCTION process_order(p_order_id INT)
RETURNS VOID AS $$
DECLARE
    v_order     orders%ROWTYPE;          -- 전체 행 타입 자동 상속
    v_price     orders.unit_price%TYPE;  -- 특정 컬럼 타입 자동 상속
    v_customer  customers.name%TYPE;     -- 다른 테이블 컬럼 타입도 가능
BEGIN
    SELECT * INTO v_order FROM orders WHERE id = p_order_id;
    v_price    := v_order.unit_price;
    
    SELECT name INTO v_customer 
    FROM customers WHERE id = v_order.customer_id;
    
    -- 처리 로직...
END;
$$ LANGUAGE plpgsql;