PostgreSQL MyBatis "invalid input syntax for type numeric" 오류 해결

1. 오류 개요

PostgreSQL과 MyBatis를 사용하여 데이터베이스 작업을 수행하는 중 "invalid input syntax for type numeric" 오류가 발생하였다.

2. 오류 발생 위치 및 원인 분석

2.1 오류 발생 위치

오류가 발생한 위치는 MyBatis SQL을 선언한 XML 파일 내의 쿼리에서 확인되었다.

문제 발생 쿼리 (일부)

update test_table
set num = nullif(#{paramNum}, '')::numeric
where id = #{id}

2.2 오류 메시지 및 원인 분석

오류 메시지: invalid input syntax for type numeric : ""

파라미터 paramNum이 빈 문자열 ('')로 전달되는 경우 발생

이미 nullif(파라미터, '') 함수를 사용하여 빈 문자열을 NULL로 변환하는 처리 적용

그러나, 동일한 SQL 문 내의 INSERT 문에서 nullif 처리가 누락된 것이 원인으로 확인됨

3. 상세 분석

PostgreSQL은 Oracle의 MERGE INTO 구문을 지원하지 않기 때문에 UPSERT 방식으로 데이터를 갱신한다. 해당 코드에서 다음과 같은 WITH upsert 구문이 사용되었다.

쿼리 내용 (상세)

WITH upsert AS (
    UPDATE test_table
    SET num = nullif(#{paramNum}, '')::numeric
    WHERE id = #{id}
)

INSERT INTO test_table (num, id)
SELECT #{paramNum}::numeric, #{id}
WHERE NOT EXISTS (SELECT * FROM upsert)

3.1 문제 발생 원인

UPDATE 문에서는 nullif(#{paramNum}, '')::numeric 처리를 적용하여 빈 문자열('')이 NULL로 변환됨.

그러나 INSERT 문에서는 #{paramNum}::numeric로 직접 변환을 수행하면서 빈 문자열('')이 numeric 타입으로 캐스팅될 때 오류 발생.

MyBatis는 쿼리를 한 줄씩 검증하며 실행하므로, 조건문 없이 그대로 INSERT 문이 생성되면서 오류가 발생함.

<if test=""> 구문을 사용하여 실행 여부를 제어하면, 동일한 상황에서 오류를 방지할 수 있음.

4. 해결 방법

4.1 INSERT 문에서도 nullif 적용

INSERT INTO test_table (num, id)
SELECT nullif(#{paramNum}, '')::numeric, #{id}
WHERE NOT EXISTS (SELECT * FROM upsert)

4.2 <if test=""> 적용하여 조건부 실행

<if test="paramNum != null and paramNum != ''">
    INSERT INTO test_table (num, id)
    SELECT #{paramNum}::numeric, #{id}
    WHERE NOT EXISTS (SELECT * FROM upsert)
</if>

5. 결론

numeric 타입의 컬럼에 빈 문자열이 입력되면 invalid input syntax for type numeric 오류가 발생할 수 있음.

nullif(#{paramNum}, '')::numeric 함수를 UPDATE뿐만 아니라 INSERT에도 적용하여 해결 가능.

<if test=""> 구문을 활용하여 빈 문자열 입력 시 INSERT 문이 생성되지 않도록 제어 가능.

해당 방식 적용 후 동일한 오류가 발생하지 않음을 확인함.