어제까지 잘 돌던 쿼리가 오늘 갑자기 ORA-01722: invalid number(수치가 부적합합니다)로 멈춘다. 쿼리는 하나도 안 바꿨는데 말이다. 이 에러는 대부분 숫자로 바꿀 수 없는 문자열이 데이터에 한 줄 들어왔을 때 터진다. 그래서 개발 DB에서는 멀쩡하고 운영 DB에서만 나는 경우가 많다.
원인 1순위
문자 컬럼을 숫자와 비교하는 암시적 형변환
결론부터 말하면, 원인 행은 VALIDATE_CONVERSION으로 찾고, 해결은 비교 대상을 문자로 맞추거나 TO_NUMBER(... DEFAULT NULL ON CONVERSION ERROR)로 막는다 (12.2부터).
재현 예제
CREATE TABLE orders (
order_id NUMBER,
amount_txt VARCHAR2(20) -- 외부 파일에서 받은 금액, 문자로 저장
);
INSERT INTO orders VALUES (1, '15000');
INSERT INTO orders VALUES (2, '23000');
INSERT INTO orders VALUES (3, '1,200'); -- 쉼표가 섞인 값
INSERT INTO orders VALUES (4, 'N/A'); -- 숫자가 아닌 값
COMMIT;
SELECT * FROM orders WHERE amount_txt > 10000;
-- ORA-01722: invalid numberamount_txt는 문자 컬럼인데 숫자 10000과 비교했다. 오라클은 이 경우 컬럼 쪽을 숫자로 바꿔서 비교한다. 그러다 '1,200'과 'N/A'에서 변환에 실패한다.
원인 4가지
1. 문자 컬럼을 숫자와 비교 (암시적 변환)
가장 흔하다. WHERE 문자컬럼 = 123처럼 따옴표 없이 숫자를 쓰면 컬럼 전체에 TO_NUMBER가 걸린 것과 같다.
2. 다른 조건으로 걸렀는데도 난다
SELECT * FROM orders
WHERE order_id IN (1, 2)
AND amount_txt > 10000;1, 2번 행만 보니 괜찮을 것 같지만, 오라클은 조건을 어떤 순서로 평가할지 보장하지 않는다. 실행계획에 따라 amount_txt > 10000을 먼저 평가하면 3, 4번 행에서 에러가 난다. 그래서 "통계가 바뀌었더니 갑자기 에러"가 생긴다.
3. 소수점·천 단위 기호
'1,200', '1.234,5'처럼 기호가 섞이면 기본 TO_NUMBER로는 안 바뀐다. 세션의 NLS_NUMERIC_CHARACTERS 설정에 따라 같은 문자열이 되기도 하고 안 되기도 한다.
4. INSERT·UPDATE에서 숫자 컬럼에 문자를 넣을 때
INSERT INTO price_master (item_id, price) SELECT item_id, amount_txt FROM stage;price가 NUMBER라면 amount_txt의 이상한 값에서 에러가 난다.
원인 행 찾기
12.2부터는 VALIDATE_CONVERSION으로 변환이 안 되는 행만 바로 골라낼 수 있다.
SELECT order_id, amount_txt
FROM orders
WHERE VALIDATE_CONVERSION(amount_txt AS NUMBER) = 0;
-- 3 1,200
-- 4 N/A그 이전 버전이라면 정규식으로 찾는다.
SELECT order_id, amount_txt
FROM orders
WHERE NOT REGEXP_LIKE(amount_txt, '^-?[0-9]+(\.[0-9]+)?$');해결 방법 3가지
1. 비교 대상을 문자로 맞추기 (가장 안전)
SELECT * FROM orders WHERE amount_txt = '15000';단, 문자 비교는 크기 비교(>, <)에서 '9'가 '10000'보다 크게 나오는 식으로 의미가 달라진다. 크기 비교가 필요하면 2번이나 3번을 쓴다.
2. 변환 실패를 NULL로 (12.2+)
SELECT *
FROM orders
WHERE TO_NUMBER(amount_txt DEFAULT NULL ON CONVERSION ERROR) > 10000;변환이 안 되는 행은 NULL이 되어 조건에서 빠진다. 쉼표가 있는 값은 형식 모델을 같이 준다.
TO_NUMBER(amount_txt DEFAULT NULL ON CONVERSION ERROR, '999,999,999')3. 데이터를 고치고 컬럼 타입을 바로잡기 (근본 해결)
숫자 값이라면 처음부터 NUMBER 컬럼에 넣는 것이 맞다. 적재 단계에서 검증하고, 기존 데이터를 정리한 뒤 타입을 바꾸는 것을 권한다.
| 방법 | 장점 | 주의 |
|---|---|---|
| 문자로 비교 | 에러 원천 차단, 인덱스 그대로 사용 | 크기 비교 의미가 달라짐 |
| DEFAULT NULL ON CONVERSION ERROR | 쿼리만 고쳐 바로 해결 | 이상 데이터가 조용히 빠짐 (로그 필요) |
| 데이터·타입 정리 | 근본 해결 | 작업량, 다른 프로그램 영향 확인 |
문자 컬럼에 WHERE 컬럼 = 123처럼 쓰면 컬럼에 함수가 걸린 것과 같아서 인덱스를 못 탈 수 있다. 에러가 안 나더라도 성능 문제의 원인이 되니, 코드 리뷰에서 암시적 형변환은 꼭 잡자.
다시 안 나게: 적재 단계에서 막기
에러를 쿼리에서 막는 것보다, 이상한 값이 처음부터 못 들어오게 하는 편이 확실하다.
-- 숫자 형식이 아니면 INSERT 자체를 거부
ALTER TABLE orders ADD CONSTRAINT chk_amount_txt
CHECK (REGEXP_LIKE(amount_txt, '^-?[0-9]+(\.[0-9]+)?$'));기존 데이터에 이미 이상한 값이 있으면 제약 조건이 걸리지 않는다. 먼저 VALIDATE_CONVERSION으로 찾은 행을 정리하거나, ENABLE NOVALIDATE로 새 데이터부터 검사하는 방법을 쓴다. 외부 파일을 받는 배치라면 적재 전에 형식 검사 단계를 하나 두고, 걸린 행은 별도 오류 테이블로 빼 두면 원인 추적도 쉬워진다.
PostgreSQL에서는
PostgreSQL은 문자와 숫자를 알아서 바꿔 주지 않는다. 그래서 같은 쿼리는 실행 전에 막힌다.
SELECT * FROM orders WHERE amount_txt > 10000;
-- ERROR: operator does not exist: character varying > integer명시적으로 바꾸면 그때 데이터 문제로 에러가 난다.
SELECT * FROM orders WHERE amount_txt::numeric > 10000;
-- ERROR: invalid input syntax for type numeric: "1,200"PostgreSQL 16부터는 pg_input_is_valid로 변환 가능한 행만 고를 수 있다.
SELECT * FROM orders
WHERE pg_input_is_valid(amount_txt, 'numeric')
AND amount_txt::numeric > 10000;오라클에서 넘어올 때 암시적 형변환에 기대던 쿼리는 PostgreSQL에서 대부분 에러가 나니, 전환 전에 문자·숫자 비교를 찾아 정리해 두는 것이 좋다.
상황별로 정리하면
| 이런 상황이라면 | 가장 흔한 원인 | 먼저 해 볼 것 |
|---|---|---|
| 개발 DB는 괜찮고 운영에서만 난다 | 운영 데이터에만 이상한 값, 또는 실행계획 차이 | 운영 데이터를 VALIDATE_CONVERSION으로 점검 |
| 통계 수집 뒤 갑자기 난다 | 조건 평가 순서가 바뀜 | 문자 비교로 바꾸거나 안전한 TO_NUMBER 사용 |
| 뷰를 조회할 때만 난다 | 뷰 병합으로 바깥 조건과 섞임 | 뷰 안의 변환을 DEFAULT NULL ON CONVERSION ERROR로 |
| 11g라 새 함수가 없다 | 버전 제한 | REGEXP_LIKE로 찾고 CASE WHEN으로 숫자일 때만 변환 |
| 배치 적재 중에 난다 | 외부 파일의 형식 오류 | 적재 전 형식 검사, 오류 행은 별도 테이블로 |
에러가 났을 때 SQL부터 고치기보다, 어떤 행이 원인인지 먼저 확인하는 습관이 시간을 가장 많이 줄여 준다. 원인 행을 보면 쉼표 문제인지, 'N/A' 같은 값인지, 빈 문자열인지가 바로 보이고 해결 방법도 그에 따라 정해진다.
함께 보면 좋은 글
- 오라클 개발자가 PostgreSQL에서 처음 헷갈리는 10가지
- 오라클 MERGE INTO 사용법, 있으면 UPDATE 없으면 INSERT와 ORA-30926 해결
- 오라클 DBMS_STATS 통계 수집 방법 (19c) (통계가 바뀌어 실행계획이 달라질 때)
참고
- Oracle 19c SQL Language Reference: TO_NUMBER
- Oracle 19c SQL Language Reference: VALIDATE_CONVERSION
- PostgreSQL: 입력 검증 함수 pg_input_is_valid (16+)
오라클 19c, PostgreSQL 16 기준으로 정리했다.
댓글