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

22000 data exception 는?

PostgreSQL 에러 코드 22000 (data exception) 은 SQL 표준에서 정의한 데이터 예외(Data Exception) 클래스의 최상위 에러로, 데이터 처리 중 값이 허용 범위를 벗어나거나 형식이 맞지 않을 때 발생합니다. 이 에러는 단독으로 발생하기보다 22001(문자열 길이 초과), 22003(숫자 범위 초과), 22P02(유효하지 않은 텍스트 표현) 등 보다 구체적인 하위 에러 코드의 부모 클래스로 작동합니다. 실무에서는 ETL 파이프라인, 데이터 마이그레이션, 사용자 입력 처리 등 다양한 상황에서 빈번하게 마주치는 에러입니다.

---

주요 발생 원인

• 데이터 타입 변환 실패 (Type Cast Failure)

가장 흔한 원인 중 하나로, 문자열을 숫자나 날짜 등 다른 타입으로 캐스팅할 때 형식이 맞지 않으면 발생합니다. 예를 들어 'abc'를 INTEGER로 변환하거나, '2024-13-45'처럼 존재하지 않는 날짜를 DATE 타입으로 캐스팅하는 경우가 대표적입니다. 외부 시스템에서 수신한 데이터나 CSV 파일을 그대로 INSERT할 때 특히 자주 발생합니다.

• 컬럼 길이 및 정밀도 초과 (Column Length / Precision Overflow)

VARCHAR(10) 컬럼에 11자 이상의 문자열을 삽입하거나, NUMERIC(5, 2) 컬럼에 999999.99처럼 정의된 정밀도를 초과하는 숫자를 저장하려 할 때 발생합니다. 특히 타 데이터베이스(MySQL, Oracle 등)에서 PostgreSQL로 마이그레이션할 때 컬럼 스펙이 미묘하게 달라 이 에러가 대량으로 발생하는 경우가 많습니다. 운영 중에 컬럼 정의를 변경하지 않고 데이터 요구사항이 바뀐 경우에도 동일한 문제가 생깁니다.

• 잘못된 날짜/시간 형식 또는 범위 (Invalid DateTime Format or Range)

PostgreSQL은 날짜와 시간 처리에 매우 엄격하여, '2024-02-30'(2월 30일), '25:00:00'(24시간 초과) 등 논리적으로 존재할 수 없는 값을 거부합니다. 또한 TIMESTAMP의 경우 허용 범위를 벗어난 값도 에러를 유발합니다. 글로벌 서비스에서 시간대(timezone) 처리가 잘못된 경우에도 이 에러 클래스가 발생할 수 있습니다.

---

해결 방법

원인 1: 데이터 타입 변환 실패 해결

TRY_CAST 패턴을 활용하거나, PostgreSQL의 예외 처리 블록을 사용하여 안전하게 변환합니다.

-- 문제가 되는 쿼리 예시
SELECT CAST('abc' AS INTEGER); -- ERROR: 22P02

-- 해결 방법 1: CASE + REGEXP를 이용한 안전한 캐스팅
SELECT
    input_value,
    CASE
        WHEN input_value ~ '^-?[0-9]+$' THEN input_value::INTEGER
        ELSE NULL
    END AS safe_integer
FROM (VALUES ('123'), ('abc'), ('456')) AS t(input_value);

-- 해결 방법 2: PostgreSQL 함수로 안전한 캐스트 래퍼 만들기
CREATE OR REPLACE FUNCTION safe_cast_to_int(p_value TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_value::INTEGER;
EXCEPTION
    WHEN data_exception THEN
        RETURN NULL; -- 변환 실패 시 NULL 반환
END;
$$;

-- 함수 사용 예시
SELECT safe_cast_to_int('123');   -- 결과: 123
SELECT safe_cast_to_int('abc');   -- 결과: NULL (에러 없음)
SELECT safe_cast_to_int('99.9'); -- 결과: NULL (정수 아님)

-- 날짜 변환 안전하게 처리하기
CREATE OR REPLACE FUNCTION safe_cast_to_date(p_value TEXT)
RETURNS DATE
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_value::DATE;
EXCEPTION
    WHEN data_exception THEN
        RETURN NULL;
END;
$$;

SELECT safe_cast_to_date('2024-01-15'); -- 결과: 2024-01-15
SELECT safe_cast_to_date('2024-02-30'); -- 결과: NULL

---

원인 2: 컬럼 길이 및 정밀도 초과 해결

INSERT/UPDATE 전에 데이터를 검증하거나, 컬럼 정의를 현실에 맞게 수정합니다.

-- 문제 상황: VARCHAR(10) 컬럼에 긴 문자열 삽입 시도
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(10) NOT NULL,
    score NUMERIC(5, 2) NOT NULL
);

-- ERROR 발생 예시
INSERT INTO users (username, score) VALUES ('verylongusername', 999.99);
-- ERROR: value too long for type character varying(10)

-- 해결 방법 1: 삽입 전 데이터 자르기 (SUBSTRING 사용)
INSERT INTO users (username, score)
VALUES (SUBSTRING('verylongusername', 1, 10), 999.99);

-- 해결 방법 2: 컬럼 크기를 현실에 맞게 변경
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(50);

-- 해결 방법 3: 삽입 전 길이 체크 쿼리로 문제 데이터 사전 확인
SELECT *
FROM staging_users
WHERE LENGTH(username) > 10
   OR score > 999.99
   OR score < -999.99;

-- NUMERIC 정밀도 문제 해결: 더 넓은 타입으로 변경
ALTER TABLE users ALTER COLUMN score TYPE NUMERIC(10, 2);

---

원인 3: 잘못된 날짜/시간 처리 해결

-- 문제가 되는 날짜 삽입 시도
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_date DATE,
    created_at TIMESTAMPTZ
);

-- ERROR: date/time field value out of range
INSERT INTO events (event_name, event_date)
VALUES ('Spring Festival', '2024-02-30');

-- 해결 방법 1: 날짜 유효성 검사 함수 활용
CREATE OR REPLACE FUNCTION is_valid_date(p_date TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM p_date::DATE;
    RETURN TRUE;
EXCEPTION
    WHEN data_exception THEN
        RETURN FALSE;
END;
$$;

-- 사용 예시
SELECT
    raw_date,
    is_valid_date(raw_date) AS is_valid
FROM (
    VALUES
        ('2024-01-15'),
        ('2024-02-30'),
        ('2024-13-01'),
        ('2024-12-31')
) AS t(raw_date);

-- 해결 방법 2: 유효한 날짜만 필터링하여 삽입
INSERT INTO events (event_name, event_date)
SELECT event_name, raw_date::DATE
FROM staging_events
WHERE is_valid_date(raw_date) = TRUE;

-- 해결 방법 3: 타임존 처리 올바르게 설정
SET timezone = 'Asia/Seoul';

INSERT INTO events (event_name, created_at)
VALUES ('KST Event', '2024-01-15 09:00:00+09:00');

-- 현재 타임존 확인
SHOW timezone;

---

예방 방법

• 입력 데이터 유효성 검사 레이어 구축 (Check Constraints + Trigger 활용)

데이터베이스 레벨에서 CHECK 제약조건을 활용하면 잘못된 데이터가 테이블에 저장되기 전에 차단할 수 있습니다. 애플리케이션 레벨의 검증만 믿지 말고, DB 자체에서도 이중으로 방어하는 것이 실무 Best Practice입니다.

“`sql

— CHECK 제약조건으로 데이터 품질 보장

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

order_date DATE NOT NULL,

quantity INTEGER NOT NULL,

unit_price NUMERIC(10, 2) NOT NULL,

status TEXT NOT NULL,

CONSTRAINT chk_quantity_positive CHECK (quantity > 0),

CONSTRAINT chk_price_positive CHECK (unit_price >= 0),

CONSTRAINT chk_status_valid CHECK (status IN (‘pending’, ‘shipped’, ‘delivered’, ‘cancelled’)),

CONSTRAINT chk_order_date_reasonable CHECK (order_date >= ‘2000-01-01’ AND order_date <= CURRENT_DATE + INTERVAL '1 year')

);

— 스테이징 테이블을 활용한 데이터 검증 패턴

CREATE TABLE staging_orders (LIKE orders); — 제약 없는 스테이징 테이블

— 검증 후 메인 테이블에 이동

INSERT INTO orders

SELECT * FROM staging_orders

WHERE quantity > 0

AND unit_price >= 0

AND status IN (‘pending’, ‘shipped’, ‘delivered’, ‘cancelled’);

“`

• 도메인(Domain) 타입 정의로 재사용 가능한 유효성 검사 표준화

PostgreSQL의 DOMAIN 기능을 활용하면 특정 형식의 데이터를 전사적으로 표준화하고, 동일한 규칙을 여러 테이블에 일관되게 적용할 수 있습니다. 코드 중복을 줄이고 유지보수성을 높이는 효과적인 방법입니다.

“`sql

— 재사용 가능한 도메인 타입 정의

CREATE DOMAIN positive_numeric AS NUMERIC(15, 2)

CHECK (VALUE > 0);

CREATE DOMAIN valid_email AS TEXT

CHECK (VALUE ~* ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’);

CREATE DOMAIN korean_phone AS TEXT

CHECK (VALUE ~ ‘^01[0-9]-[0-9]{4}-[0-9]{4}$’);

— 도메인 타입을 여러 테이블에 재사용

CREATE TABLE customers (

id SERIAL PRIMARY KEY,

email valid_email NOT NULL,

phone korean_phone,

credit_limit positive_numeric DEFAULT 1000000.00

);

CREATE TABLE products (

id SERIAL PRIMARY KEY,

price positive_numeric NOT NULL,

cost positive_numeric NOT NULL

);

“`

---

관련 에러

| 에러 코드 | 에러명 | 설명 |

|———–|——–|——|

| 22001 | string_data_right_truncation | 문자열이 컬럼 최대 길이를 초과할 때 |

| 22003 | numeric_value_out_of_range | 숫자 값이 컬럼의 허용 범위를 벗어날 때 |

| 22007 | invalid_datetime_format | 날짜/시간 형식이 올바르지 않을 때 |

| 22008 | datetime_field_overflow | 날짜/시간 값이 허용 범위를 초과할 때 |

| 22012 | division_by_zero | 0으로 나누기 연산 수행 시 |

| 22P02 | invalid_text_representation | 텍스트를 특정 타입으로 변환할 수 없을 때 |

| 22P03 | invalid_binary_representation | 바이너리 데이터 형식이 유효하지 않을 때 |

22000 클래스 에러는 PostgreSQL 공식 문서의 [Appendix A: Error Codes](https://www.postgresql.org/docs/current/errcodes-appendix.html)에서 전체 목록을 확인할 수 있으며, PL/pgSQL에서는 WHEN data_exception THEN 구문으로 이 클래스 전체를 한 번에 핸들링할 수 있습니다.

---

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