报错案例

{
    "code": 500,
    "msg": "\n### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column \"event_list\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 281\n### The error may exist in com/uinfor/mes/base/dao/UcodeBusPageMapper.java (best guess)\n### The error may involve com.uinfor.mes.base.dao.UcodeBusPageMapper.insert-Inline\n### The error occurred while setting parameters\n### SQL: INSERT INTO base_ucode_bus_page  ( id, page_number, page_name,   layout_param, layout_html_id, layout_html, page_type, page_class, page_style, remarks, event_list, method_list, create_by, create_time, update_by, update_time )  VALUES  ( ?, ?, ?,   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )\n### Cause: org.postgresql.util.PSQLException: ERROR: column \"event_list\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 281\n; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column \"event_list\" is of type json but expression is of type character varying\n  Hint: You will need to rewrite or cast the expression.\n  Position: 281",
    "data": null,
    "success": false,
    "time": 1669601863
}

如果是使用mybatisplus的内置方法,则需要在实体字段加上@TableField注解,并且需要在类名上启动@TableName(autoResultMap = true)

// autoResultMap = true 必须写,否则无法识别
@TableName(autoResultMap = true)
public class BlogUser implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;

    private String name;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    private Integer version;
    // 使用类型转换,否则无法增删改查
    @TableField(typeHandler= JsonTypeHandler.class)
    private Map<String,Object> relation;
}

如果是写在xml里面,则必须在对应字段注明转换器class:

<insert id="addxml" parameterType="com.hou.postgresql.blog.entity.po.BlogUser">
        INSERT INTO blog_user (name, relation, fans, birthday, points, login_time, write_interval, numbers, adult, address, weight)
        VALUES (#{name},
        /*必须显式的指明转换器,否则编译过程就会报错,主要是List,map这种数组,jsonb对应的实体类型*/
        #{relation,typeHandler=com.hou.postgresql.handler.JsonTypeHandler},
        #{fans,typeHandler=com.hou.postgresql.handler.ArrayTypeHandler}, #{birthday}, #{points}, #{loginTime}, #{writeInterval}::interval,
        #{numbers,typeHandler=com.hou.postgresql.handler.ArrayTypeHandler}, #{adult}, #{address}, #{weight})

</insert>

column is of type jsonb but expression is of type character varying问题

即使写了转换器,查询的时候没问题,但是插入的时候依然会报这个错,这时需要在连接的url后面加上参数stringtype=unspecified就可以正常添加了

url: jdbc:postgresql://192.168.1.11:5432/postgres?currentSchema=sys&stringtype=unspecified

二、schemas问题

pgsql默认的是public,如果用mybatisplus的内置方法的话,是需要指定连接的currentSchema的,否则只会默认查询public,自己写sql可以在前面加上schemas 但是使用内置方法没有,必须在连接url指定schemsa,否则会报ERROR: relation "item" does not exist表不存在

三、所有数据类型参数格式

url后面加上stringtype=unspecified就可以使用任意格式插入了,除了json和array之外,其他的特殊类型,比如地址,间隔,时间等都可以使用string 参数如下:

{
  "address": "192.168.1.70",   // inet
  "adult": false,  // boolean
  "birthday": "1994-12-16",  // date
  "fans": ["zhangpeng","zhouhang","pengle"],
  "loginTime": "09:12",  // time
  "name": "侯征",
  "numbers": [12,56,42],   // array
  "points": 10.522,  // numeric
  "relation": {       // jsonb
      "key": "value"
  },
  "weight": "[45,50]",    // 区间
  "writeInterval": "800"  // 时间间隔,单位秒
}