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

22007 invalid datetime format 는?

PostgreSQL 에러 코드 22007은 invalid datetime format으로, 날짜/시간 데이터를 처리하는 과정에서 입력된 문자열이 PostgreSQL이 기대하는 날짜/시간 형식과 일치하지 않을 때 발생합니다. 예를 들어 DATE, TIMESTAMP, TIMESTAMPTZ, TIME 등의 타입으로 캐스팅하거나 삽입할 때 형식이 맞지 않으면 이 에러가 트리거됩니다. 실무에서는 외부 시스템으로부터 데이터를 수집하거나 ETL 파이프라인에서 날짜 문자열을 변환할 때 매우 빈번하게 마주치는 에러입니다.

---

주요 발생 원인

• 잘못된 날짜/시간 문자열 형식 사용

가장 흔한 원인으로, 애플리케이션이나 외부 데이터 소스에서 넘어온 날짜 문자열이 PostgreSQL의 기본 ISO 8601 형식(YYYY-MM-DD, YYYY-MM-DD HH:MI:SS)과 다를 때 발생합니다. 예를 들어 '20231025', '25/10/2023', 'October 25, 2023'처럼 슬래시 구분자나 월 이름 포함 형식, 또는 구분자 없는 숫자 나열 형식이 그대로 DATE 컬럼에 입력되면 에러가 발생합니다. 특히 미국식 날짜 형식(MM/DD/YYYY)과 유럽식 날짜 형식(DD/MM/YYYY)을 혼용하면 파싱 실패가 발생하거나, 심각한 경우 데이터가 잘못 해석될 수 있습니다.

• TO_DATE() / TO_TIMESTAMP() 함수의 포맷 마스크 불일치

PostgreSQL의 TO_DATE(), TO_TIMESTAMP() 함수는 두 번째 인자로 포맷 마스크를 받습니다. 이때 첫 번째 인자인 문자열의 실제 형식과 두 번째 인자의 포맷 마스크가 일치하지 않으면 22007 에러가 발생합니다. 예를 들어 입력 문자열은 '2023-10-25 14:30:00' 형태인데 포맷을 'MM/DD/YYYY'로 지정하면 파싱에 실패합니다. 또한 시간대(timezone) 정보가 포함된 문자열을 TO_TIMESTAMP()로 파싱할 때 포맷 마스크에 TZ나 OF 지시자를 빠뜨리는 경우도 이 에러의 원인이 됩니다.

• 타입 캐스팅 시 암묵적 변환 실패

PostgreSQL에서 ::DATE, ::TIMESTAMP, ::TIMESTAMPTZ 같은 캐스트 연산자나 CAST() 함수를 사용할 때, 소스 문자열이 PostgreSQL의 내부 파서가 자동으로 인식할 수 없는 형식이면 22007 에러가 발생합니다. PostgreSQL의 날짜 파서는 꽤 유연하지만 '2023.10.25'처럼 점(.) 구분자를 사용하거나 '25-Oct-23'처럼 2자리 연도와 월 약자를 혼합한 형식은 파싱에 실패할 수 있습니다. 특히 ORM(Object-Relational Mapping) 도구나 BI 툴에서 자동 생성된 쿼리에서 이런 형식이 그대로 전달될 때 운영 환경에서 갑작스럽게 에러가 발생하는 경우가 많습니다.

---

해결 방법

원인 1 해결: 표준 ISO 8601 형식으로 변환 후 삽입

가장 안전한 방법은 데이터를 삽입하기 전에 PostgreSQL이 기본적으로 인식하는 ISO 8601 형식으로 정규화하는 것입니다.

-- 잘못된 예: 슬래시 구분자 사용 (에러 발생)
INSERT INTO orders (order_date) VALUES ('25/10/2023');
-- ERROR:  invalid input syntax for type date: "25/10/2023"

-- 해결: ISO 8601 형식 사용
INSERT INTO orders (order_date) VALUES ('2023-10-25');

-- 해결: TO_DATE() 함수로 명시적 변환
INSERT INTO orders (order_date)
VALUES (TO_DATE('25/10/2023', 'DD/MM/YYYY'));

-- 미국식 MM/DD/YYYY 형식 변환
INSERT INTO orders (order_date)
VALUES (TO_DATE('10/25/2023', 'MM/DD/YYYY'));

-- 구분자 없는 숫자 형식 처리
INSERT INTO orders (order_date)
VALUES (TO_DATE('20231025', 'YYYYMMDD'));

원인 2 해결: TO_TIMESTAMP() 포맷 마스크 정확히 지정

-- 잘못된 예: 포맷 마스크 불일치 (에러 발생)
SELECT TO_TIMESTAMP('2023-10-25 14:30:00', 'MM/DD/YYYY');
-- ERROR:  invalid value "" for "MM"

-- 해결: 실제 문자열 형식에 맞는 포맷 마스크 사용
SELECT TO_TIMESTAMP('2023-10-25 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- 결과: 2023-10-25 14:30:00+09

-- 시간대 정보가 포함된 문자열 처리
SELECT TO_TIMESTAMP('2023-10-25 14:30:00 +09:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
-- 결과: 2023-10-25 14:30:00+09

-- 12시간제 AM/PM 포함 형식
SELECT TO_TIMESTAMP('10/25/2023 02:30:00 PM', 'MM/DD/YYYY HH12:MI:SS AM');
-- 결과: 2023-10-25 14:30:00+09

-- 월 이름 포함 형식 (영문)
SELECT TO_TIMESTAMP('October 25, 2023 14:30', 'Month DD, YYYY HH24:MI');
-- 결과: 2023-10-25 14:30:00+09

원인 3 해결: 암묵적 캐스트 대신 명시적 변환 함수 사용

-- 잘못된 예: 점(.) 구분자로 인한 캐스트 실패
SELECT '2023.10.25'::DATE;
-- ERROR:  invalid input syntax for type date: "2023.10.25"

-- 해결: REPLACE()로 구분자 정규화 후 캐스트
SELECT REPLACE('2023.10.25', '.', '-')::DATE;
-- 결과: 2023-10-25

-- 여러 종류의 구분자를 처리하는 범용 함수 생성
CREATE OR REPLACE FUNCTION safe_to_date(p_date_str TEXT)
RETURNS DATE AS $$
BEGIN
    -- ISO 형식 먼저 시도
    BEGIN
        RETURN p_date_str::DATE;
    EXCEPTION WHEN invalid_datetime_format OR invalid_text_representation THEN
        NULL;
    END;
    -- DD/MM/YYYY 형식 시도
    BEGIN
        RETURN TO_DATE(p_date_str, 'DD/MM/YYYY');
    EXCEPTION WHEN invalid_datetime_format THEN
        NULL;
    END;
    -- MM/DD/YYYY 형식 시도
    BEGIN
        RETURN TO_DATE(p_date_str, 'MM/DD/YYYY');
    EXCEPTION WHEN invalid_datetime_format THEN
        NULL;
    END;
    -- 점 구분자 처리
    BEGIN
        RETURN TO_DATE(REPLACE(p_date_str, '.', '-'), 'YYYY-MM-DD');
    EXCEPTION WHEN invalid_datetime_format THEN
        NULL;
    END;
    -- 모든 형식 실패 시 NULL 반환
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 함수 테스트
SELECT safe_to_date('2023-10-25');   -- 2023-10-25
SELECT safe_to_date('25/10/2023');   -- 2023-10-25
SELECT safe_to_date('2023.10.25');   -- 2023-10-25
SELECT safe_to_date('invalid');      -- NULL

-- 배치 데이터 정제 쿼리: 잘못된 날짜 데이터 찾기
SELECT raw_date_string,
       safe_to_date(raw_date_string) AS parsed_date,
       CASE
           WHEN safe_to_date(raw_date_string) IS NULL THEN 'INVALID'
           ELSE 'VALID'
       END AS validation_status
FROM staging_orders
WHERE raw_date_string IS NOT NULL;

---

예방 방법

• 입력 레이어에서 날짜 형식 유효성 검사 및 제약조건 적용

애플리케이션 레벨에서 데이터베이스로 날짜 값을 전달하기 전에 항상 ISO 8601 형식으로 정규화하는 것이 최선입니다. 데이터베이스 레이어에서도 CHECK 제약조건이나 트리거를 활용하여 잘못된 형식의 데이터가 저장되지 않도록 방어 로직을 추가해야 합니다.

“`sql

— CHECK 제약조건을 통한 날짜 형식 강제 (텍스트 컬럼에 날짜 저장 시)

ALTER TABLE event_log

ADD CONSTRAINT chk_event_date_format

CHECK (event_date_str ~ ‘^\d{4}-\d{2}-\d{2}$’);

— 입력 정규화 트리거 생성

CREATE OR REPLACE FUNCTION normalize_date_input()

RETURNS TRIGGER AS $$

BEGIN

— 슬래시 구분자를 하이픈으로 자동 변환

IF NEW.order_date_str ~ ‘^\d{2}/\d{2}/\d{4}$’ THEN

NEW.order_date = TO_DATE(NEW.order_date_str, ‘DD/MM/YYYY’);

ELSIF NEW.order_date_str ~ ‘^\d{4}\d{2}\d{2}$’ THEN

NEW.order_date = TO_DATE(NEW.order_date_str, ‘YYYYMMDD’);

ELSE

NEW.order_date = NEW.order_date_str::DATE;

END IF;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

“`

• DateStyle 설정 명시적으로 관리하고 파라미터화된 쿼리 사용

PostgreSQL의 DateStyle 설정은 서버 또는 세션 수준에서 날짜 입출력 형식에 영향을 줍니다. 팀 내 모든 개발 환경과 운영 환경에서 동일한 DateStyle을 사용하도록 postgresql.conf 또는 연결 초기화 시 명시적으로 설정해야 합니다. 또한 날짜 값은 반드시 파라미터화된 쿼리(Prepared Statement)를 통해 전달하여 드라이버가 타입 변환을 안전하게 처리하도록 위임하는 것이 좋습니다.

“`sql

— 세션 수준에서 DateStyle 명시적 설정

SET datestyle = ‘ISO, YMD’;

— postgresql.conf 권장 설정

— datestyle = ‘iso, ymd’

— 현재 DateStyle 확인

SHOW datestyle;

— 애플리케이션 연결 초기화 시 설정 (예: 연결 문자열에 options 추가)

— postgresql://user:pass@host/db?options=-c%20datestyle%3Diso%2Cymd

“`

---

관련 에러

• 22008 (datetime_field_overflow): 날짜/시간 형식은 맞지만 실제 값이 허용 범위를 벗어났을 때 발생합니다. 예를 들어 월 값으로 13이나 일 값으로 32가 입력된 경우입니다.
• 22P02 (invalid_text_representation): 날짜뿐만 아니라 정수, UUID, ENUM 등 다양한 타입에 대한 텍스트-타입 변환 실패 시 발생하며, 22007과 함께 ETL 작업에서 자주 나타납니다.
• 42804 (datatype_mismatch): 날짜 타입과 다른 타입 간의 연산 또는 함수 호출 시 타입이 맞지 않을 때 발생하며, 날짜 관련 컬럼 비교 시 주의해야 합니다.

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