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

22018 invalid character value for cast 는?

PostgreSQL 에러 코드 22018 (invalid_character_value_for_cast)는 문자열 값을 다른 데이터 타입으로 명시적 또는 암묵적으로 변환(CAST)할 때, 변환 대상 타입이 허용하지 않는 문자 또는 형식이 포함된 경우 발생합니다. 예를 들어 'abc'라는 문자열을 INTEGER나 NUMERIC 타입으로 캐스팅하려 할 때, 혹은 유효하지 않은 날짜 문자열을 DATE 타입으로 변환하려 할 때 이 에러가 발생합니다. 이 에러는 데이터 마이그레이션, ETL 파이프라인, 사용자 입력 처리 과정에서 특히 자주 등장하며, 데이터 품질 문제의 신호로 받아들여야 합니다.

---

주요 발생 원인

1. 숫자형 타입으로의 잘못된 문자열 캐스팅

가장 흔한 원인으로, 숫자처럼 보이지만 실제로는 공백, 쉼표, 통화 기호($, ₩) 등 특수 문자가 포함된 문자열을 INTEGER, NUMERIC, FLOAT 등의 숫자형으로 변환하려 할 때 발생합니다. 외부 시스템이나 CSV 파일에서 가져온 데이터에는 이러한 오염된 값이 포함되는 경우가 매우 많으며, 이를 사전 정제 없이 직접 캐스팅하면 반드시 에러가 발생합니다.

-- 에러 발생 예시
SELECT CAST('1,234' AS INTEGER);
-- ERROR: invalid input syntax for type integer: "1,234"

SELECT CAST('$500' AS NUMERIC);
-- ERROR: invalid input syntax for type numeric: "$500"

SELECT CAST('3.14abc' AS FLOAT);
-- ERROR: invalid input syntax for type double precision: "3.14abc"

2. 날짜/시간 타입으로의 잘못된 포맷 변환

DATE, TIMESTAMP, TIME 등의 날짜·시간 타입으로 캐스팅할 때 PostgreSQL이 인식하지 못하는 포맷의 문자열을 입력하면 이 에러가 발생합니다. 예를 들어 '20231301'처럼 월이 13인 논리적으로 불가능한 날짜나, '2023/99/01'처럼 잘못된 구분자와 범위를 가진 값이 대표적입니다. 글로벌 서비스에서 서로 다른 로케일의 날짜 포맷이 혼재하는 경우에도 자주 발생합니다.

-- 에러 발생 예시
SELECT CAST('2023-13-01' AS DATE);
-- ERROR: date/time field value out of range: "2023-13-01"

SELECT CAST('01/32/2023' AS DATE);
-- ERROR: date/time field value out of range: "01/32/2023"

SELECT CAST('not-a-date' AS TIMESTAMP);
-- ERROR: invalid input syntax for type timestamp: "not-a-date"

3. ENUM 또는 사용자 정의 타입으로의 잘못된 변환

PostgreSQL에서 사용자가 정의한 ENUM 타입이나 도메인(Domain) 타입으로 캐스팅할 때, ENUM에 정의되지 않은 값을 입력하면 이 에러가 발생합니다. 예를 들어 'active', 'inactive'만 정의된 ENUM 타입에 'ACTIVE'(대소문자 불일치)나 'deleted'(미정의 값)를 캐스팅하려 하면 실패합니다. ENUM 타입은 대소문자를 구분하므로, 애플리케이션에서 값을 정규화하지 않으면 이런 문제가 자주 발생합니다.

-- ENUM 타입 생성 예시
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending');

-- 에러 발생 예시
SELECT CAST('ACTIVE' AS user_status);
-- ERROR: invalid input value for enum user_status: "ACTIVE"

SELECT CAST('deleted' AS user_status);
-- ERROR: invalid input value for enum user_status: "deleted"

---

해결 방법

원인 1 해결: 숫자형 캐스팅 오류 수정

REGEXP_REPLACE를 사용하여 숫자가 아닌 문자를 제거하거나, NULLIF와 TRY_CAST 패턴을 사용하여 안전하게 변환하세요. PostgreSQL에는 내장 TRY_CAST가 없으므로, 아래처럼 직접 함수를 만들어 사용하는 것이 실무에서 매우 유용합니다.

-- 방법 1: REGEXP_REPLACE로 숫자 외 문자 제거 후 캐스팅
SELECT CAST(REGEXP_REPLACE('1,234,567', '[^0-9.]', '', 'g') AS NUMERIC);
-- 결과: 1234567

SELECT CAST(REGEXP_REPLACE('$1,500.50', '[^0-9.]', '', 'g') AS NUMERIC);
-- 결과: 1500.50

-- 방법 2: 안전한 캐스팅 함수 (TRY_CAST 구현)
CREATE OR REPLACE FUNCTION safe_cast_to_numeric(p_value TEXT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_value::NUMERIC;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 사용 예시
SELECT safe_cast_to_numeric('1,234');   -- 결과: NULL (에러 대신)
SELECT safe_cast_to_numeric('1234');    -- 결과: 1234

-- 방법 3: CASE WHEN + 정규식으로 유효성 검사 후 캐스팅
SELECT
    original_value,
    CASE
        WHEN original_value ~ '^-?[0-9]+(\.[0-9]+)?$'
        THEN original_value::NUMERIC
        ELSE NULL
    END AS safe_numeric
FROM (VALUES ('1234'), ('abc'), ('99.5'), ('$100')) AS t(original_value);

원인 2 해결: 날짜/시간 캐스팅 오류 수정

TO_DATE() 또는 TO_TIMESTAMP() 함수를 사용하면 포맷 문자열을 명시적으로 지정할 수 있어, 다양한 포맷의 날짜 문자열을 안전하게 변환할 수 있습니다.

-- 방법 1: TO_DATE로 포맷 명시
SELECT TO_DATE('20231201', 'YYYYMMDD');
-- 결과: 2023-12-01

SELECT TO_DATE('01/12/2023', 'DD/MM/YYYY');
-- 결과: 2023-12-01

-- 방법 2: TO_TIMESTAMP로 다양한 포맷 처리
SELECT TO_TIMESTAMP('2023-12-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- 결과: 2023-12-01 14:30:00+00

-- 방법 3: 안전한 날짜 변환 함수
CREATE OR REPLACE FUNCTION safe_cast_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 OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 사용 예시
SELECT safe_cast_to_date('2023-13-01', 'YYYY-MM-DD'); -- 결과: NULL
SELECT safe_cast_to_date('2023-12-01', 'YYYY-MM-DD'); -- 결과: 2023-12-01

-- 방법 4: 배치 데이터 정제 시 유효하지 않은 날짜 필터링
SELECT *
FROM raw_data
WHERE safe_cast_to_date(date_column) IS NOT NULL;

원인 3 해결: ENUM 캐스팅 오류 수정

-- 방법 1: 입력값 정규화 (소문자 변환 후 캐스팅)
SELECT LOWER('ACTIVE')::user_status;
-- 결과: active

-- 방법 2: CASE WHEN으로 매핑 테이블 처리
SELECT
    username,
    CASE LOWER(raw_status)
        WHEN 'active'   THEN 'active'::user_status
        WHEN 'enabled'  THEN 'active'::user_status   -- 동의어 매핑
        WHEN 'inactive' THEN 'inactive'::user_status
        WHEN 'disabled' THEN 'inactive'::user_status -- 동의어 매핑
        ELSE            'pending'::user_status        -- 기본값
    END AS normalized_status
FROM raw_users;

-- 방법 3: ENUM 유효값 확인 후 캐스팅
SELECT
    input_val,
    CASE
        WHEN input_val IN (
            SELECT enumlabel FROM pg_enum
            JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
            WHERE pg_type.typname = 'user_status'
        )
        THEN input_val::user_status
        ELSE NULL
    END AS safe_status
FROM (VALUES ('active'), ('ACTIVE'), ('unknown')) AS t(input_val);

---

예방 방법

1. 데이터 입력 시점에서의 유효성 검사 및 CHECK 제약 조건 활용

데이터가 테이블에 삽입되기 전, 애플리케이션 레이어와 데이터베이스 레이어 양쪽에서 유효성을 검사하는 이중 방어 전략을 사용하세요. 테이블에 CHECK 제약 조건을 추가하고, 입력 함수나 트리거를 통해 정규화 로직을 강제하면 오염된 데이터가 DB에 저장되는 것을 원천 차단할 수 있습니다.

-- CHECK 제약 조건으로 숫자 형식 강제
ALTER TABLE orders
ADD CONSTRAINT chk_amount_format
CHECK (amount_text ~ '^[0-9]+(\.[0-9]{1,2})?$');

-- 날짜 형식 검증 트리거
CREATE OR REPLACE FUNCTION validate_date_format()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.birth_date_text !~ '^\d{4}-\d{2}-\d{2}$' THEN
        RAISE EXCEPTION 'Invalid date format: %. Expected YYYY-MM-DD', NEW.birth_date_text;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. ETL 파이프라인에서 스테이징 테이블과 데이터 정제 단계 분리

원본 데이터를 직접 운영 테이블에 적재하지 말고, 모든 컬럼을 TEXT 타입으로 선언한 스테이징 테이블에 먼저 적재한 후, 정제 및 검증 단계를 거쳐 운영 테이블로 이동하는 두 단계 적재 전략을 사용하세요. 이 방식은 캐스팅 에러로 인한 전체 배치 실패를 방지하고, 문제 데이터를 별도 로그 테이블에 기록하여 추적 가능성을 높여줍니다.

-- 스테이징 테이블 (모든 타입을 TEXT로 받음)
CREATE TABLE stg_orders (
    order_id    TEXT,
    amount      TEXT,
    order_date  TEXT,
    status      TEXT,
    loaded_at   TIMESTAMP DEFAULT NOW()
);

-- 정제 후 운영 테이블로 이동
INSERT INTO orders (order_id, amount, order_date, status)
SELECT
    order_id::INTEGER,
    safe_cast_to_numeric(amount),
    safe_cast_to_date(order_date),
    LOWER(status)::user_status
FROM stg_orders
WHERE safe_cast_to_numeric(amount) IS NOT NULL
  AND safe_cast_to_date(order_date) IS NOT NULL;

---

관련 에러

• 22007 (invalid_datetime_format): 날짜/시간 포맷이 잘못된 경우로, 22018과 함께 날짜 처리 오류에서 자주 쌍으로 등장합니다.
• 22003 (numeric_value_out_of_range): 캐스팅 자체는 성공하지만 대상 타입의 범위를 초과하는 경우 발생합니다.
• 23502 (not_null_violation): 안전 캐스팅 함수로 NULL을 반환했을 때 NOT NULL 제약이 있는 컬럼에 삽입하면 발생할 수 있습니다.
• 42846 (cannot_coerce): 두 타입 간 캐스팅 경로 자체가 존재하지 않을 때 발생하는 에러입니다.

---

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