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

22007 invalid datetime format 는?

PostgreSQL 에러 코드 22007은 invalid datetime format으로, 날짜/시간 값을 특정 타입으로 변환하거나 입력할 때 형식이 올바르지 않을 경우 발생합니다. 예를 들어 DATE, TIMESTAMP, TIMESTAMPTZ, TIME 등의 컬럼에 잘못된 문자열 형식의 값을 삽입하거나 캐스팅하려 할 때 이 에러가 트리거됩니다. 주로 애플리케이션에서 날짜 포맷을 통일하지 않거나, 외부 데이터 소스에서 가져온 비표준 날짜 문자열을 그대로 사용할 때 실무에서 빈번하게 나타납니다.

---

주요 발생 원인

• 잘못된 날짜 문자열 형식 직접 삽입

가장 흔한 원인으로, '2024/03/15', '15-03-2024', 'March 15 2024' 등 PostgreSQL이 기본적으로 인식하지 못하는 포맷의 문자열을 DATE나 TIMESTAMP 컬럼에 직접 넣으려 할 때 발생합니다. PostgreSQL은 ISO 8601 표준(YYYY-MM-DD)을 기본으로 따르기 때문에, 이와 다른 형식은 명시적인 변환 없이는 처리되지 않습니다. 특히 엑셀이나 CSV 파일에서 날짜 데이터를 가져올 때 이 케이스가 매우 자주 발생합니다.

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

TO_DATE('2024-03-15', 'MM/DD/YYYY')처럼 실제 날짜 문자열의 구분자나 순서와 포맷 마스크가 서로 맞지 않으면 22007 에러가 발생합니다. 포맷 마스크의 구분자(슬래시, 하이픈, 점 등)와 실제 데이터의 구분자가 다를 경우에도 동일한 에러가 발생하므로, 변환 함수를 사용할 때는 반드시 실제 데이터 형식을 먼저 확인해야 합니다. 이 실수는 대량 데이터를 마이그레이션하는 과정에서 수백만 건의 에러를 한꺼번에 만들어낼 수 있어 특히 위험합니다.

• 타임존 정보 포함 문자열의 부적절한 처리

TIMESTAMP WITHOUT TIME ZONE 컬럼에 '2024-03-15 10:30:00+09'처럼 타임존 오프셋이 포함된 문자열을 삽입하거나, 반대로 TIMESTAMPTZ 컬럼에 타임존 정보가 전혀 없는 문자열을 넣으면서 서버 설정과 충돌이 발생할 때도 이 에러가 트리거될 수 있습니다. 또한 '2024-03-15T10:30:00Z'와 같은 ISO 8601 UTC 표기법을 일부 버전이나 설정에서 처리할 때도 주의가 필요합니다.

---

해결 방법

원인 1 해결: 표준 형식으로 변환하거나 TO_DATE 활용

잘못된 형식의 날짜 문자열을 올바르게 변환하려면 TO_DATE() 또는 TO_TIMESTAMP() 함수를 사용하여 포맷을 명시적으로 지정해야 합니다.

-- 에러 발생 예시
INSERT INTO orders (order_date) VALUES ('15/03/2024');
-- ERROR:  invalid input syntax for type date: "15/03/2024"

-- 해결책 1: TO_DATE() 함수 사용
INSERT INTO orders (order_date) VALUES (TO_DATE('15/03/2024', 'DD/MM/YYYY'));

-- 해결책 2: 표준 ISO 8601 형식으로 변경
INSERT INTO orders (order_date) VALUES ('2024-03-15');

-- 해결책 3: 명시적 CAST 사용 (표준 형식일 때)
INSERT INTO orders (order_date) VALUES ('2024-03-15'::DATE);

-- 다양한 비표준 형식 처리 예시
SELECT TO_DATE('2024.03.15', 'YYYY.MM.DD');        -- 점 구분자
SELECT TO_DATE('20240315', 'YYYYMMDD');             -- 구분자 없음
SELECT TO_DATE('March 15, 2024', 'Month DD, YYYY'); -- 영문 월 이름

원인 2 해결: 포맷 마스크를 실제 데이터에 맞게 수정

TO_DATE() 및 TO_TIMESTAMP() 함수의 포맷 마스크를 실제 데이터 형식과 정확히 일치시켜야 합니다.

-- 에러 발생 예시: 구분자 불일치
SELECT TO_DATE('2024-03-15', 'MM/DD/YYYY');
-- ERROR:  invalid value "03-" for "MM/"

-- 해결책: 실제 데이터 형식에 맞는 포맷 마스크 사용
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD');   -- 올바른 예
SELECT TO_DATE('03/15/2024', 'MM/DD/YYYY');   -- 올바른 예 (미국식)
SELECT TO_DATE('15.03.2024', 'DD.MM.YYYY');   -- 올바른 예 (유럽식)

-- TIMESTAMP 변환 예시
SELECT TO_TIMESTAMP('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('15/03/2024 02:30 PM', 'DD/MM/YYYY HH12:MI AM');

-- 대량 데이터 마이그레이션 시 안전한 변환 패턴
UPDATE legacy_table
SET new_date_col = TO_DATE(old_date_string, 'DD-MON-YYYY')
WHERE old_date_string IS NOT NULL
  AND old_date_string ~ '^\d{2}-[A-Z]{3}-\d{4}$';

원인 3 해결: 타임존 처리 명확화

타임존 관련 문제는 컬럼 타입과 입력 데이터의 타임존 정보를 일치시켜야 합니다.

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

-- TIMESTAMPTZ 컬럼에 올바른 삽입 방법
INSERT INTO events (event_time) 
VALUES ('2024-03-15 10:30:00+09:00'::TIMESTAMPTZ);

-- AT TIME ZONE 활용
INSERT INTO events (event_time)
VALUES (('2024-03-15 10:30:00' AT TIME ZONE 'Asia/Seoul'));

-- TIMESTAMP WITHOUT TIME ZONE에는 타임존 오프셋 제거
INSERT INTO local_events (event_time)
VALUES ('2024-03-15 10:30:00'::TIMESTAMP);

-- 타임존 변환 후 삽입
INSERT INTO events (event_time)
SELECT ('2024-03-15 10:30:00+09'::TIMESTAMPTZ) AT TIME ZONE 'UTC';

-- 기존 데이터의 타임존 일괄 변환
UPDATE events
SET event_time = event_time AT TIME ZONE 'UTC'
WHERE event_time IS NOT NULL;

---

예방 방법

• 애플리케이션 레벨에서 날짜 형식 표준화 및 입력 유효성 검사 적용

애플리케이션에서 PostgreSQL로 날짜 데이터를 전달할 때는 항상 ISO 8601 형식(YYYY-MM-DD 또는 YYYY-MM-DD HH:MI:SS)을 사용하도록 코딩 컨벤션을 정립하세요. ORM(예: SQLAlchemy, Hibernate)을 사용하는 경우 네이티브 날짜/시간 객체를 그대로 바인딩하여 문자열 변환 과정을 없애는 것이 가장 안전합니다. 또한 CHECK 제약조건이나 트리거를 활용하여 DB 레벨에서도 이중으로 검증하는 방어적 아키텍처를 채택하세요.

“`sql

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

ALTER TABLE orders

ADD CONSTRAINT chk_order_date

CHECK (order_date >= ‘2000-01-01’ AND order_date <= '2099-12-31');

“`

• 배치/마이그레이션 작업 전 데이터 프로파일링과 스테이징 테스트 필수화

대량의 외부 데이터를 적재하기 전에 반드시 소수의 샘플 데이터로 먼저 변환 테스트를 수행하고, REGEXP_MATCHES()나 CASE WHEN을 활용해 다양한 날짜 포맷을 미리 분류 및 표준화하는 전처리 단계를 ETL 파이프라인에 포함시키세요.

“`sql

— 데이터 적재 전 포맷 분류 쿼리

SELECT

CASE

WHEN date_col ~ ‘^\d{4}-\d{2}-\d{2}$’ THEN ‘ISO_FORMAT’

WHEN date_col ~ ‘^\d{2}/\d{2}/\d{4}$’ THEN ‘US_FORMAT’

WHEN date_col ~ ‘^\d{2}\.\d{2}\.\d{4}$’ THEN ‘EU_FORMAT’

ELSE ‘UNKNOWN’

END AS format_type,

COUNT(*) AS cnt

FROM staging_table

GROUP BY format_type;

“`

---

관련 에러

• 22008 datetime_field_overflow: 날짜/시간 값이 허용 범위를 초과했을 때 발생합니다. 예를 들어 월에 13, 일에 32 등 논리적으로 불가능한 값을 입력하면 트리거됩니다.
• 22P02 invalid_text_representation: 텍스트를 숫자나 다른 기본 타입으로 변환할 때 형식이 맞지 않는 경우 발생하며, 22007과 함께 데이터 타입 변환 관련 에러 그룹에 속합니다.
• 42883 undefined_function: 날짜 변환 함수를 잘못된 인자 타입으로 호출할 때 발생할 수 있으며, 22007 에러를 우회하려다 잘못된 함수를 호출하면 연쇄적으로 나타나기도 합니다.
• 22P02 invalid_text_representation: 'abc'::DATE처럼 날짜로 해석될 수 없는 완전히 잘못된 문자열을 캐스팅할 때 나타나며, 22007보다 더 근본적인 형식 오류 상황에서 발생합니다.

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