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

22018 invalid character value for cast 는?

PostgreSQL 에러 코드 22018은 invalid character value for cast로, 특정 문자열 값을 다른 데이터 타입으로 변환(CAST)할 때 해당 값이 대상 타입에 맞지 않는 경우 발생합니다. 예를 들어, 숫자가 아닌 문자열을 INTEGER나 NUMERIC 타입으로 캐스팅하거나, 올바른 날짜 형식이 아닌 문자열을 DATE 타입으로 변환하려 할 때 이 에러가 발생합니다. 데이터 마이그레이션, ETL 파이프라인, 또는 사용자 입력값을 처리하는 애플리케이션에서 특히 자주 마주치는 에러입니다.

---

주요 발생 원인

• 숫자형 타입으로의 잘못된 문자열 변환

가장 흔한 원인으로, 'abc', '12.34.56', '1,000'처럼 순수하게 숫자로 파싱될 수 없는 문자열을 INTEGER, BIGINT, NUMERIC, FLOAT 등의 숫자형 타입으로 강제 변환할 때 발생합니다. 데이터베이스에 외부 시스템에서 가져온 원시 텍스트 데이터를 저장한 뒤 이를 숫자로 처리하려는 ETL 작업에서 빈번하게 나타납니다.

• 날짜/시간 타입으로의 잘못된 문자열 변환

'2024/13/45', '31-Feb-2024', 'not-a-date'처럼 유효하지 않은 날짜 문자열을 DATE, TIMESTAMP, TIMESTAMPTZ 타입으로 변환하려 할 때 발생합니다. 국가별로 날짜 형식(MM/DD/YYYY vs DD/MM/YYYY)이 다르기 때문에, 다국적 서비스나 레거시 시스템에서 데이터를 가져올 때 이 문제가 특히 자주 발생합니다.

• 불리언(Boolean) 및 기타 특수 타입으로의 잘못된 변환

PostgreSQL의 BOOLEAN 타입은 'true', 'false', 't', 'f', 'yes', 'no', '1', '0' 등 제한된 값만 허용합니다. 'Y'나 'N', 'TRUE_VALUE'처럼 PostgreSQL이 인식하지 못하는 문자열을 BOOLEAN으로 캐스팅하면 이 에러가 발생합니다. 또한 UUID, INET, CIDR 등 형식이 엄격한 타입으로 변환할 때도 마찬가지입니다.

---

해결 방법

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

REGEXP_REPLACE나 NULLIF, CASE WHEN 구문을 활용해 안전하게 변환합니다.

-- 문제 발생 예시
SELECT CAST('1,234' AS INTEGER); -- ERROR: 22018

-- 해결 방법 1: 쉼표 제거 후 변환
SELECT CAST(REPLACE('1,234', ',', '') AS INTEGER); -- 결과: 1234

-- 해결 방법 2: 변환 가능 여부를 정규식으로 검사 후 변환
SELECT
    CASE
        WHEN col ~ '^-?[0-9]+(\.[0-9]+)?$' THEN col::NUMERIC
        ELSE NULL
    END AS safe_numeric
FROM raw_data;

-- 해결 방법 3: PostgreSQL 에러를 잡아서 NULL 반환 (함수 활용)
CREATE OR REPLACE FUNCTION safe_cast_to_integer(p_value TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_value::INTEGER;
EXCEPTION
    WHEN invalid_character_value_for_cast OR numeric_value_out_of_range THEN
        RETURN NULL;
END;
$$;

-- 사용 예시
SELECT safe_cast_to_integer('abc');   -- NULL 반환
SELECT safe_cast_to_integer('1234');  -- 1234 반환
SELECT safe_cast_to_integer('1,234'); -- NULL 반환

원인 2 해결: 날짜/시간 타입 변환 오류

-- 문제 발생 예시
SELECT CAST('2024/13/45' AS DATE); -- ERROR: 22018

-- 해결 방법 1: TO_DATE 함수로 형식 명시
SELECT TO_DATE('31/12/2024', 'DD/MM/YYYY'); -- 결과: 2024-12-31

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

-- 사용 예시
SELECT safe_cast_to_date('2024-12-31');           -- 2024-12-31
SELECT safe_cast_to_date('31/12/2024', 'DD/MM/YYYY'); -- 2024-12-31
SELECT safe_cast_to_date('not-a-date');            -- NULL 반환

-- 해결 방법 3: 대용량 테이블에서 잘못된 날짜 필터링 후 처리
SELECT *
FROM raw_import_table
WHERE col_date ~ '^\d{4}-\d{2}-\d{2}$'  -- 기본 형식 검사
  AND col_date::DATE IS NOT NULL;          -- 실제 유효성 검사

원인 3 해결: Boolean 및 특수 타입 변환 오류

-- 문제 발생 예시
SELECT CAST('Y' AS BOOLEAN); -- ERROR: 22018

-- 해결 방법 1: CASE WHEN으로 명시적 변환
SELECT
    CASE
        WHEN upper(flag_col) IN ('Y', 'YES', '1', 'TRUE', 'T') THEN TRUE
        WHEN upper(flag_col) IN ('N', 'NO', '0', 'FALSE', 'F') THEN FALSE
        ELSE NULL
    END AS bool_flag
FROM source_table;

-- 해결 방법 2: UUID 변환 안전 처리
CREATE OR REPLACE FUNCTION safe_cast_to_uuid(p_value TEXT)
RETURNS UUID
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_value::UUID;
EXCEPTION
    WHEN invalid_character_value_for_cast THEN
        RETURN NULL;
END;
$$;

-- 해결 방법 3: 배치 데이터 정제 쿼리 (실무 활용)
UPDATE staging_table
SET
    numeric_col = CASE WHEN raw_numeric ~ '^-?[0-9]+$' THEN raw_numeric::INTEGER ELSE NULL END,
    date_col    = safe_cast_to_date(raw_date),
    bool_col    = CASE WHEN upper(raw_bool) IN ('Y','YES','1','TRUE') THEN TRUE
                       WHEN upper(raw_bool) IN ('N','NO','0','FALSE') THEN FALSE
                       ELSE NULL END
WHERE processed = FALSE;

---

예방 방법

• 데이터 입력 시점에서의 유효성 검사 및 제약 조건 설정

데이터가 테이블에 적재되기 전에 CHECK 제약 조건이나 트리거를 활용하여 형식이 올바른 데이터만 저장되도록 강제합니다. 또한 애플리케이션 레이어에서도 DB에 전달하기 전에 값의 형식을 사전 검증하는 로직을 추가하면 에러 발생 확률을 크게 줄일 수 있습니다.

“`sql

— CHECK 제약 조건으로 숫자 형식 강제

ALTER TABLE orders

ADD CONSTRAINT chk_amount_format

CHECK (raw_amount ~ ‘^[0-9]+(\.[0-9]{1,2})?$’);

“`

• 안전한 캐스팅 함수 라이브러리 구축 및 표준화

팀 전체가 공유하는 safe_cast_* 유틸리티 함수 세트를 별도의 스키마(예: util 스키마)에 정의하고, 모든 ETL 파이프라인과 마이그레이션 스크립트에서 직접 CAST() 또는 :: 연산자 대신 이 함수들을 사용하도록 코딩 가이드라인을 수립합니다. 이를 통해 에러 처리 방식을 일관되게 유지하고, 문제 발생 시 한 곳에서 수정할 수 있습니다.

“`sql

— util 스키마에 안전 변환 함수 모음 생성

CREATE SCHEMA IF NOT EXISTS util;

CREATE OR REPLACE FUNCTION util.to_int(p_val TEXT) RETURNS INTEGER

LANGUAGE plpgsql AS $$

BEGIN RETURN p_val::INTEGER;

EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$;

CREATE OR REPLACE FUNCTION util.to_date(p_val TEXT) RETURNS DATE

LANGUAGE plpgsql AS $$

BEGIN RETURN p_val::DATE;

EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$;

“`

---

관련 에러

• 22007 invalid_datetime_format: 날짜/시간 문자열의 형식 자체가 잘못된 경우로, 22018과 함께 자주 발생합니다.
• 22003 numeric_value_out_of_range: 숫자 변환은 성공했으나 대상 타입의 범위를 초과한 경우입니다(예: INTEGER 최대값 초과).
• 22P02 invalid_text_representation: 텍스트를 특정 타입(UUID, INET 등)으로 변환할 때 형식이 맞지 않는 경우이며, 22018과 혼용되어 나타나는 경우가 많습니다.
• 42846 cannot_coerce: 서로 호환되지 않는 타입 간 캐스팅을 시도할 때 발생하며, 캐스팅 자체가 정의되지 않은 경우입니다.

---

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