[JPA/PostgreSQL] ERROR: function lower(bytea) does not exist 해결기

[JPA/PostgreSQL] ERROR: function lower(bytea) does not exist 해결기 — IS NULL 위치를 맨 뒤로

PostgreSQL + Spring Data JPA 환경에서 아래와 같은 에러를 만난다면:

org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist

대부분 LOWER(:param)가 bytea로 추론되어 발생한 문제입니다.
저는 is null 조건을 가장 마지막으로 이동해 해결했습니다. 이 글에서는 증상 → 원인 → 해결 → 대안 순서로 정리합니다. (참고 링크 포함)

---

증상

다음과 같은 JPQL/Repository 쿼리를 사용할 때 발생합니다:

@Query("""
SELECT e
FROM Example e
WHERE (:name IS NULL OR LOWER(e.name) = LOWER(:name))
""")
Page<Example> search(@Param("name") String name, Pageable pageable);

위 쿼리를 PostgreSQL에서 실행하면, LOWER(:name) 호출 시 파라미터 타입이 잘못 추론되어 **lower(bytea)**를 호출하려다 실패합니다.

---

원인 (요점 정리)

• Hibernate는 동일 파라미터의 SQL 타입을 한 번만 추론하는데,
  :name IS NULL 식에서 :name의 타입을 확정하기가 애매합니다.
• 그 결과 뒤쪽의 LOWER(:name)에서도 **varchar 대신 bytea**로 해석될 수 있고,
  PostgreSQL에는 lower(bytea) 함수가 없어 에러가 납니다. (Stack Overflow)

스택오버플로의 상세 분석: 파라미터 :createdBy가
:createdBy is null OR LOWER(o.createdBy) = LOWER(:createdBy)처럼 두 번 등장할 때,
첫 등장에서 타입을 확정하지 못하면 이후에도 잘못된 타입(예: bytea)로 굳어집니다. (Stack Overflow)

---

해결 1) IS NULL 조건을 맨 뒤로 이동 (제가 쓴 실전 해결책)

핵심 아이디어:
파라미터의 첫 등장을 LOWER(:name) 같은 문자열 문맥으로 오게 하여,
Hibernate가 해당 파라미터를 varchar로 올바르게 추론하도록 유도합니다.

Before (문제 발생 가능)

@Query("""
SELECT e
FROM Example e
WHERE (:name IS NULL OR LOWER(e.name) = LOWER(:name))
""")
Page<Example> search(@Param("name") String name, Pageable pageable);

After (해결)

@Query("""
SELECT e
FROM Example e
WHERE (LOWER(e.name) = LOWER(:name) OR :name IS NULL)
""")
Page<Example> search(@Param("name") String name, Pageable pageable);

논리식은 동일하지만, 파라미터가 처음 등장하는 위치가 다릅니다.
이렇게 바꾸면 :name이 처음에 문자열 비교(LOWER) 문맥으로 등장 → varchar로 정확히 추론 → 에러 해결.

---

대안들 (상황에 따라 선택)

아래 방법들은 동일 문제를 피하려는 추가 선택지입니다.

대안 A) 명시적 캐스팅으로 타입 확정

@Query("""
SELECT e
FROM Example e
WHERE (:name IS NULL OR LOWER(e.name) = LOWER(CAST(:name AS string)))
""")

Hibernate/JPA 방언마다 캐스팅 문법이 다를 수 있습니다. 필요 시 네이티브 쿼리로 전환해 CAST(:name AS varchar)를 쓰는 방법도 있습니다. (타입 캐스팅으로 해결하는 일반적인 접근) (Vlad Mihalcea)

대안 B) 파라미터 분리 (IS NULL 체크용 vs 비교용)

@Query("""
SELECT e
FROM Example e
WHERE (:nameForCompare IS NOT NULL AND LOWER(e.name) = LOWER(:nameForCompare))
   OR (:nameIsNull = TRUE)
""")
Page<Example> search(
  @Param("nameForCompare") String nameForCompare,
  @Param("nameIsNull") Boolean nameIsNull,
  Pageable pageable
);

조금 번거롭지만, 서로 다른 파라미터로 타입/용도를 분리하면 추론 충돌을 피할 수 있습니다.

대안 C) 스프링 데이터 SpEL로 조건 분기

@Query("""
SELECT e
FROM Example e
WHERE :#{#name == null ? true : false} = TRUE
   OR LOWER(e.name) = LOWER(:#{#name})
""")

SpEL로 널일 때 전체를 패스시키는 형태. 쿼리 가독성/이식성은 조금 떨어질 수 있습니다.

---

예시 Repository 코드 (일반화 버전)

@Repository
public interface ExampleRepository extends JpaRepository<Example, Long> {

    // ✅ 권장: LOWER(...) 비교가 먼저, IS NULL은 맨 뒤
    @Query("""
    SELECT e
    FROM Example e
    WHERE (LOWER(e.name) = LOWER(:name) OR :name IS NULL)
    """)
    Page<Example> findAllByNameNullable(
            @Param("name") String name,
            Pageable pageable);

    // ⛔ 문제가 될 수 있는 형태 (IS NULL이 앞)
    @Query("""
    SELECT e
    FROM Example e
    WHERE (:name IS NULL OR LOWER(e.name) = LOWER(:name))
    """)
    Page<Example> findAllByNameNullable_OrderSensitive(
            @Param("name") String name,
            Pageable pageable);
}

---

정리

• 문제는 파라미터 타입 추론 타이밍/맥락에서 비롯됩니다.
• 첫 등장 위치를 조절해 varchar로 추론되게 만들면 간단히 해결됩니다.
• 상황에 따라 명시적 캐스팅, 파라미터 분리, SpEL 분기도 대안이 됩니다.

---

참고 링크

• Stack Overflow — 상세 원인 분석 및 사례
  “org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist”
  (Stack Overflow)
• (관련 이슈) Spring Data JPA에서 LOWER + IS NULL 조합 시 PostgreSQL 오류 보고
  (GitHub)
• (배경 지식) PostgreSQL에서 타입/연산자/캐스팅 이슈 다루기 (일반론) — Vlad Mihalcea 블로그
  (Vlad Mihalcea)