operator does not exist: character varying = bytea ERROR: numeric field overflow

迷南。 2022-05-16 08:23 181阅读 0赞

2018-08-18 10:27:40.842  WARN 6492 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 22003
    2018-08-18 10:27:40.842 ERROR 6492 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: numeric field overflow
      详细：A field with precision 3, scale 2 must round to an absolute value less than 10^1.
    2018-08-18 10:27:40.842 ERROR 6492 --- [nio-8082-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet] with root cause
    
    org.postgresql.util.PSQLException: ERROR: numeric field overflow
      详细：A field with precision 3, scale 2 must round to an absolute value less than 10^1.

**1、ERROR: numeric field overflow  数字字段溢出   ==== 这是前端传过来的时候，报的错误**

### 【这是在一段时间之内的分段时间的多点查询语句所报的错误】 ###

**2、然后本人自己测试报错如下：**

2018-08-18 10:40:46.583  WARN 6492 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42883
    2018-08-18 10:40:46.583 ERROR 6492 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: operator does not exist: character varying = bytea
      建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
      位置：155
    2018-08-18 10:40:46.586 ERROR 6492 --- [nio-8082-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
    
    org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
      建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
      位置：155

![70][]

controller 代码改为：

@GetMapping("/selectReport/day")
        public  List selectMoreTimeDay(@RequestParam("tag") String tag){
            //截取数组
            String[] tags  = tag.split(",");
            //转化成list类型
            List<String> tagList = Arrays.asList(tags);
            return metricsRepository.selectMoreTimeDay(tagList);

顺利的出结果数据！！

**3、再和前端进行测试： controller 代码**

@GetMapping("/selectReport/day")
        public  List selectMoreTimeDay(@RequestParam("tag") String tag){
            //json 字符串转换成java对象数组
            JSONArray json = JSONArray.fromObject(tag);
            List<String> tags = (List<String>)JSONArray.toCollection(json, String.class);
            return  metricsRepository.selectMoreTimeDay(tags);
        }

# 总结： #

因为tag 值是多个的，且tag 过长、在查询的过程中，SQL语句在数据库里进行查询【查询多个tag值】，就会出现报错

![70 1][]

**但是查询一个tag值就可以查询出数据来。 遇到数字字段溢出 这种问题的时候，可以先去debug一下【如果是与前端做数据交互的时候，自己用postman也是可以测试的】，看看接收参数的类型等对不对。然后再查询一下自己写的SQL语句有没有问题。**

### 出现问题的原因： ###

**SQL 语句中 ： cast(avg(value) as decimal(3,2))   as avg\_value  这个的问题  应改为round（cast(avg(value) as decimal），2)就可以查出数据了**

[70]: /images/20220516/547f888385654da0a1391f3698e1eafd.png
[70 1]: /images/20220516/86ae241f72f243829e9ffa0fd5506709.png