PostgreSQL更新时bytea与double precision类型不匹配异常修复咨询

问题场景 #

实体类使用BigDecimal类型映射PostgreSQL的double precision字段，执行Spring Data JPA原生SQL更新操作时，抛出类型不匹配异常，提示参数被识别为bytea类型无法匹配double precision列。

实体类代码 #

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@Entity
@Table(name = "words")
public class Words implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private long id;

    @Column(name = "keyword", length = 200, unique = true)
    private String keyword;

    @Column(name = "low_range", length = 100)
    private BigDecimal lowRange;

    @Column(name = "high_range", length = 100)
    private BigDecimal highRange;
}

Spring Data JPA原生更新语句 #

@Modifying
@Query(value="update processed_words set low_range = :lowRange, high_range = :highRange where keyword = :keyword", nativeQuery = true)
int updateByKeyword(@Param("keyword") String keyword,
                    @Param("lowRange") BigDecimal lowRange,
                    @Param("highRange") BigDecimal highRange);

PostgreSQL表结构 #

CREATE TABLE IF NOT EXISTS public.words
(
    id bigint NOT NULL DEFAULT nextval('words_id_seq'::regclass),
    keyword character varying(200) COLLATE pg_catalog."default",
    high_range double precision,
    low_range double precision
)

异常信息 #

Caused by: org.postgresql.util.PSQLException: ERROR: column "low_range" is of type double precision but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

解决方案 #

1. 原生SQL中显式转换参数类型 #

在更新语句中通过cast函数将BigDecimal参数强制转换为double precision，同时修正表名（原语句中processed_words应为实际表名words）：

@Modifying
@Query(value="update words set low_range = cast(:lowRange as double precision), high_range = cast(:highRange as double precision) where keyword = :keyword", nativeQuery = true)
int updateByKeyword(@Param("keyword") String keyword,
                    @Param("lowRange") BigDecimal lowRange,
                    @Param("highRange") BigDecimal highRange);

2. 优化实体类字段映射配置 #

在实体类的BigDecimal字段上明确指定数据库列类型，移除不必要的length属性（double precision类型无需长度配置），让JPA自动处理类型适配：

@Column(name = "low_range", columnDefinition = "double precision")
private BigDecimal lowRange;

@Column(name = "high_range", columnDefinition = "double precision")
private BigDecimal highRange;

3. 改用JPQL语句代替原生SQL #

JPQL会基于实体类映射自动生成正确的类型转换逻辑，无需手动处理数据库类型适配：

@Modifying
@Query("update Words w set w.lowRange = :lowRange, w.highRange = :highRange where w.keyword = :keyword")
int updateByKeyword(@Param("keyword") String keyword,
                    @Param("lowRange") BigDecimal lowRange,
                    @Param("highRange") BigDecimal highRange);

内容的提问来源于stack exchange，提问作者Peter Penzov