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

22P02
2026년 08월 18일 | DBMS Error 가이드

이 글에서 다루는 내용

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

22P02 invalid text representation 는?

PostgreSQL 에러 코드 22P02 (invalid_text_representation) 는 문자열 값을 특정 데이터 타입으로 변환(캐스팅)하려 할 때, 해당 문자열이 대상 타입의 유효한 형식을 따르지 않을 경우 발생합니다. 예를 들어, 'abc'라는 문자열을 INTEGER 타입으로 변환하거나, '2024-13-45'처럼 존재하지 않는 날짜를 DATE 타입에 넣으려 할 때 이 에러가 발생합니다. 주로 애플리케이션에서 사용자 입력값을 충분한 검증 없이 SQL 쿼리에 직접 사용하거나, 외부 데이터 소스에서 가져온 데이터를 그대로 INSERT/UPDATE할 때 자주 마주치게 됩니다.

주요 발생 원인

1. 숫자형 컬럼에 비숫자 문자열 삽입 또는 캐스팅 시도

가장 흔한 원인으로, INTEGER, NUMERIC, BIGINT 등의 숫자형 컬럼에 'N/A', 'unknown', '-', 빈 문자열('') 같은 비숫자 값을 삽입하거나 캐스팅하려 할 때 발생합니다. 특히 CSV 파일이나 외부 API에서 데이터를 가져올 때 숫자가 누락된 자리를 의미 있는 문자열로 채워놓은 경우가 많아 실무에서 매우 빈번히 발생하는 케이스입니다.

2. UUID 또는 ENUM 타입에 잘못된 형식의 값 입력

PostgreSQL의 UUID 타입은 '550e8400-e29b-41d4-a716-446655440000'과 같이 엄격한 형식을 요구하며, 형식이 조금이라도 맞지 않으면 즉시 에러가 발생합니다. 마찬가지로 사용자 정의 ENUM 타입에 정의되지 않은 값을 삽입하려 할 때도 이 에러가 발생할 수 있으며, 대소문자까지 정확히 일치해야 하기 때문에 놓치기 쉬운 원인입니다.

3. 날짜/시간 타입에 잘못된 형식의 문자열 전달

DATE, TIMESTAMP, TIMESTAMPTZ 컬럼에 '20240101'(구분자 없음), '01/13/2024'(MM/DD/YYYY 형식), '2024년 1월 13일' 같은 PostgreSQL이 인식하지 못하는 형식의 날짜 문자열을 전달할 때 발생합니다. 특히 다국적 시스템을 운영하거나 여러 국가의 날짜 형식이 혼재하는 환경에서는 이 원인으로 인한 에러가 빈번하게 발생합니다.

해결 방법

원인 1 해결: 숫자형 변환 오류

CASE문이나 NULLIF, REGEXP_REPLACE 등을 활용해 변환 전에 값을 정제합니다.

-- 문제 발생 예시
SELECT CAST('N/A' AS INTEGER);
-- ERROR:  invalid input syntax for type integer: "N/A"

-- 해결책 1: CASE 문으로 비숫자 값을 NULL로 처리
SELECT CASE
           WHEN value ~ '^-?[0-9]+(\.[0-9]+)?$' THEN value::NUMERIC
           ELSE NULL
       END AS safe_number
FROM raw_data;

-- 해결책 2: NULLIF + 정규식을 이용한 안전한 변환
SELECT NULLIF(REGEXP_REPLACE(value, '[^0-9.\-]', '', 'g'), '')::NUMERIC
FROM raw_data;

-- 해결책 3: PostgreSQL 에러를 잡는 함수 만들기
CREATE OR REPLACE FUNCTION safe_to_integer(p_value TEXT)
RETURNS INTEGER AS $$
BEGIN
    RETURN p_value::INTEGER;
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 사용 예시
SELECT safe_to_integer('123');   -- 결과: 123
SELECT safe_to_integer('N/A');   -- 결과: NULL
SELECT safe_to_integer('');      -- 결과: NULL

원인 2 해결: UUID / ENUM 형식 오류

-- UUID 형식 검증 후 삽입
-- 문제 발생 예시
INSERT INTO users (id) VALUES ('not-a-valid-uuid');
-- ERROR: invalid input syntax for type uuid: "not-a-valid-uuid"

-- 해결책 1: UUID 형식 정규식 검증
SELECT CASE
           WHEN value ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
               THEN value::UUID
           ELSE NULL
       END AS safe_uuid
FROM raw_data;

-- 해결책 2: gen_random_uuid()로 새 UUID 생성 (삽입 시)
INSERT INTO users (id, name)
VALUES (gen_random_uuid(), 'Hong Gildong');

-- ENUM 타입 관련 해결책
-- 현재 정의된 ENUM 값 확인
SELECT enumlabel
FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'your_enum_type_name';

-- 안전한 ENUM 삽입 (존재하는 값인지 먼저 확인)
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_enum e
        JOIN pg_type t ON e.enumtypid = t.oid
        WHERE t.typname = 'status_type'
          AND e.enumlabel = 'active'
    ) THEN
        INSERT INTO orders (status) VALUES ('active'::status_type);
    ELSE
        RAISE NOTICE 'ENUM 값이 존재하지 않습니다.';
    END IF;
END $$;

원인 3 해결: 날짜/시간 형식 오류

-- 문제 발생 예시
SELECT '20240113'::DATE;
-- ERROR: invalid input syntax for type date: "20240113"

-- 해결책 1: TO_DATE 함수로 형식 명시
SELECT TO_DATE('20240113', 'YYYYMMDD');          -- 결과: 2024-01-13
SELECT TO_DATE('01/13/2024', 'MM/DD/YYYY');      -- 결과: 2024-01-13
SELECT TO_DATE('2024년 1월 13일', 'YYYY"년" MM"월" DD"일"'); -- 결과: 2024-01-13

-- 해결책 2: TO_TIMESTAMP로 TIMESTAMPTZ 변환
SELECT TO_TIMESTAMP('2024-01-13 15:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- 해결책 3: 안전한 날짜 변환 함수
CREATE OR REPLACE FUNCTION safe_to_date(p_value TEXT, p_format TEXT DEFAULT 'YYYY-MM-DD')
RETURNS DATE AS $$
BEGIN
    RETURN TO_DATE(p_value, p_format);
EXCEPTION
    WHEN invalid_text_representation OR invalid_datetime_format THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 사용 예시
SELECT safe_to_date('2024-01-13');          -- 결과: 2024-01-13
SELECT safe_to_date('invalid-date');         -- 결과: NULL
SELECT safe_to_date('13/01/2024', 'DD/MM/YYYY'); -- 결과: 2024-01-13

-- 배치 데이터 정제 시 활용
UPDATE staging_table
SET clean_date = safe_to_date(raw_date_column)
WHERE raw_date_column IS NOT NULL;

예방 방법

1. 애플리케이션 레벨에서 CHECK 제약조건과 도메인 타입 활용

데이터가 DB에 도달하기 전에 PostgreSQL의 CHECK 제약조건과 사용자 정의 DOMAIN 타입을 적극 활용하여 잘못된 형식의 데이터 자체를 차단하는 것이 최선입니다. 이렇게 하면 에러가 발생하더라도 어느 컬럼, 어느 제약조건에서 걸렸는지 명확한 메시지를 받을 수 있어 디버깅이 훨씬 쉬워집니다.

-- 사용자 정의 도메인으로 양수 정수만 허용
CREATE DOMAIN positive_integer AS INTEGER
    CHECK (VALUE > 0);

-- 특정 형식의 전화번호만 허용하는 도메인
CREATE DOMAIN phone_number AS TEXT
    CHECK (VALUE ~ '^\d{3}-\d{4}-\d{4}$');

-- 테이블 생성 시 적용
CREATE TABLE employees (
    id          positive_integer PRIMARY KEY,
    phone       phone_number,
    hire_date   DATE NOT NULL CHECK (hire_date >= '2000-01-01')
);

2. 외부 데이터 수집 시 스테이징 테이블 + 검증 파이프라인 구축

CSV, API, ETL 파이프라인 등 외부 소스에서 데이터를 가져올 때는 모든 컬럼을 TEXT 타입으로 받는 스테이징 테이블을 먼저 활용하고, 검증 및 변환 후 실제 테이블에 INSERT하는 2단계 파이프라인을 구축하는 것이 좋습니다. 이를 통해 22P02를 포함한 다양한 데이터 품질 문제를 사전에 잡아낼 수 있습니다.

-- 스테이징 테이블 (모든 컬럼 TEXT)
CREATE TABLE staging_orders (
    order_id    TEXT,
    amount      TEXT,
    order_date  TEXT,
    status      TEXT
);

-- 검증 후 실제 테이블로 이동
INSERT INTO orders (order_id, amount, order_date, status)
SELECT
    safe_to_integer(order_id),
    safe_to_numeric(amount),
    safe_to_date(order_date),
    CASE WHEN status IN ('pending','completed','cancelled')
         THEN status::order_status
         ELSE NULL END
FROM staging_orders
WHERE order_id ~ '^\d+$'           -- 숫자 형식 검증
  AND amount ~ '^\d+(\.\d+)?$'     -- 소수점 포함 숫자 검증
  AND safe_to_date(order_date) IS NOT NULL; -- 날짜 변환 가능 여부 확인