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

22005
2026년 06월 05일 | DBMS Error 가이드

이 글에서 다루는 내용

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

22005 error in assignment 는?

PostgreSQL 에러 코드 22005, error in assignment는 데이터를 특정 컬럼이나 변수에 할당(assignment)하는 과정에서 데이터 타입이 맞지 않거나 변환이 불가능할 때 발생하는 에러입니다. 주로 INSERT, UPDATE 구문 또는 PL/pgSQL 함수 내부에서 변수에 값을 대입할 때 소스 값이 대상 타입으로 암묵적으로 변환될 수 없는 경우에 트리거됩니다. SQL 표준 상태 코드 클래스 22(Data Exception)에 속하며, 데이터 무결성과 타입 안전성을 보장하기 위해 PostgreSQL 엔진이 엄격하게 검사하는 영역입니다.

주요 발생 원인

1. 호환되지 않는 데이터 타입 간 암묵적 변환 실패

가장 빈번한 원인입니다. 예를 들어, INTEGER 타입 컬럼에 알파벳 문자열을 직접 삽입하거나, DATE 타입 컬럼에 포맷이 올바르지 않은 문자열을 넣을 때 발생합니다. PostgreSQL은 명시적 캐스팅 없이는 타입 간 자동 변환을 허용하지 않으며, 이 경계를 넘으려 할 때 22005 에러가 발생합니다.

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

PL/pgSQL 블록 안에서 DECLARE 섹션에 선언된 변수의 타입과 실제 할당하려는 값의 타입이 일치하지 않을 때 발생합니다. 특히 %TYPE 또는 %ROWTYPE 어트리뷰트를 사용하지 않고 타입을 하드코딩했을 경우, 테이블 스키마 변경 이후 함수가 여전히 구버전 타입을 참조하고 있어 에러가 발생하는 사례가 많습니다.

3. 도메인(Domain) 또는 커스텀 타입 제약 조건 위반

PostgreSQL의 도메인(Domain)은 기본 타입 위에 추가적인 제약 조건을 부여한 사용자 정의 타입입니다. 도메인에 정의된 CHECK 제약이나 NOT NULL 조건을 만족하지 못하는 값을 할당할 경우 22005 에러가 발생할 수 있습니다. 이 경우는 단순한 타입 오류보다 발견하기 어렵고, 도메인 정의를 직접 확인해야 원인을 파악할 수 있습니다.

해결 방법

원인 1: 타입 불일치 해결 – 명시적 캐스팅(CAST) 사용

문제 상황:

-- 에러 발생 예시: INTEGER 컬럼에 문자열 삽입 시도
CREATE TABLE orders (
    order_id   INTEGER,
    quantity   INTEGER,
    order_date DATE
);

-- 아래 구문은 22005 에러를 유발할 수 있음
INSERT INTO orders (order_id, quantity, order_date)
VALUES ('abc', '열다섯', '2024/13/01');

해결 방법:

-- 명시적 CAST를 사용하여 타입을 맞춤
INSERT INTO orders (order_id, quantity, order_date)
VALUES (
    CAST('12345' AS INTEGER),
    CAST('15' AS INTEGER),
    TO_DATE('2024-01-13', 'YYYY-MM-DD')
);

-- PostgreSQL 스타일 캐스팅 연산자 :: 사용
INSERT INTO orders (order_id, quantity, order_date)
VALUES (
    '12345'::INTEGER,
    '15'::INTEGER,
    '2024-01-13'::DATE
);

또한 pg_cast 시스템 카탈로그를 통해 어떤 타입 간 변환이 지원되는지 미리 확인할 수 있습니다:

-- 특정 타입 간 캐스트 지원 여부 확인
SELECT
    pg_catalog.format_type(castsource, NULL) AS source_type,
    pg_catalog.format_type(casttarget, NULL) AS target_type,
    castcontext
FROM pg_cast
WHERE pg_catalog.format_type(castsource, NULL) = 'text'
  AND pg_catalog.format_type(casttarget, NULL) = 'integer';

원인 2: PL/pgSQL 함수 변수 타입 불일치 해결

문제 상황:

-- 에러를 유발하는 함수 예시
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    v_total  VARCHAR(20);  -- 잘못된 타입 선언 (실제 컬럼은 NUMERIC)
BEGIN
    SELECT total_amount INTO v_total
    FROM orders
    WHERE order_id = p_order_id;

    RETURN v_total;  -- 여기서 22005 발생 가능
END;
$$ LANGUAGE plpgsql;

해결 방법:

-- %TYPE 어트리뷰트를 사용하여 컬럼 타입을 자동으로 참조
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    -- 테이블 컬럼 타입을 그대로 참조하여 타입 불일치 방지
    v_total  orders.total_amount%TYPE;
BEGIN
    SELECT total_amount INTO v_total
    FROM orders
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- 또는 %ROWTYPE을 사용하여 전체 행 구조를 참조
CREATE OR REPLACE FUNCTION get_order_row(p_order_id INTEGER)
RETURNS orders AS $$
DECLARE
    v_row  orders%ROWTYPE;
BEGIN
    SELECT * INTO v_row
    FROM orders
    WHERE order_id = p_order_id;

    RETURN v_row;
END;
$$ LANGUAGE plpgsql;

원인 3: 도메인 제약 조건 위반 해결

도메인 정의 확인:

-- 현재 데이터베이스의 도메인 목록과 제약 조건 확인
SELECT
    domain_name,
    data_type,
    domain_default,
    check_clause
FROM information_schema.domains d
LEFT JOIN information_schema.domain_constraints dc
    ON d.domain_name = dc.domain_name
LEFT JOIN information_schema.check_constraints cc
    ON dc.constraint_name = cc.constraint_name
WHERE d.domain_schema = 'public';

문제 상황 및 해결:

-- 양수만 허용하는 도메인 예시
CREATE DOMAIN positive_int AS INTEGER
    CHECK (VALUE > 0);

CREATE TABLE inventory (
    item_id  SERIAL PRIMARY KEY,
    stock    positive_int
);

-- 아래는 22005 에러 발생 (도메인 CHECK 위반)
INSERT INTO inventory (stock) VALUES (-5);

-- 해결: 값을 도메인 제약에 맞게 수정
INSERT INTO inventory (stock) VALUES (5);

-- 도메인 제약 자체를 수정해야 하는 경우
ALTER DOMAIN positive_int DROP CONSTRAINT positive_int_check;
ALTER DOMAIN positive_int ADD CONSTRAINT positive_int_check CHECK (VALUE >= 0);

예방 방법

1. 입력 데이터 유효성 검사 레이어 구축

애플리케이션 레이어와 데이터베이스 레이어 양쪽에서 입력 데이터의 타입과 형식을 사전에 검증하는 습관을 들이십시오. 특히 외부 시스템으로부터 데이터를 수신할 때는 데이터베이스에 삽입하기 전에 pg_input_is_valid() 함수(PostgreSQL 16+)를 활용하거나, TRY...CATCH 패턴을 PL/pgSQL 안에서 적극적으로 사용하는 것을 권장합니다.

-- PostgreSQL 16+: 삽입 전 값 유효성 사전 확인
SELECT pg_input_is_valid('abc', 'integer');  -- false 반환
SELECT pg_input_is_valid('123', 'integer');  -- true 반환

-- PL/pgSQL에서 예외 처리 패턴
CREATE OR REPLACE FUNCTION safe_insert_order(
    p_order_id TEXT,
    p_quantity TEXT
) RETURNS BOOLEAN AS $$
BEGIN
    INSERT INTO orders (order_id, quantity)
    VALUES (p_order_id::INTEGER, p_quantity::INTEGER);
    RETURN TRUE;
EXCEPTION
    WHEN invalid_parameter_value OR numeric_value_out_of_range THEN
        RAISE WARNING '잘못된 입력값: order_id=%, quantity=%', p_order_id, p_quantity;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

2. 스키마 변경 시 의존 객체 일괄 검토 및 재컴파일

테이블의 컬럼 타입을 변경할 경우, 해당 테이블을 참조하는 모든 함수, 뷰, 트리거를 반드시 재검토하고 필요 시 재컴파일해야 합니다. %TYPE과 %ROWTYPE을 사용하면 스키마 변경에 자동으로 대응되지만, 그렇지 않은 경우 아래 쿼리로 의존성을 추적하십시오.

-- 특정 테이블에 의존하는 함수 목록 조회
SELECT DISTINCT
    p.proname AS function_name,
    pg_catalog.pg_get_function_identity_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_depend d ON d.objid = p.oid
JOIN pg_class c ON c.oid = d.refobjid
WHERE c.relname = 'orders'  -- 변경된 테이블명
  AND c.relkind = 'r';