ERROR: column “xxx“ is of type date but expression is of type character varying

问题### Cause: org.postgresql.util.PSQLException: ERROR: column "take_effect_start_time" is of type date but expression is of type character varying建议：You will need to rewrite or cast the expression.位置：

西门不止会吹雪

6458人浏览 · 2022-05-01 09:00:00

西门不止会吹雪 · 2022-05-01 09:00:00 发布

问题

### Cause: org.postgresql.util.PSQLException: ERROR: column "take_effect_start_time" is of type date but expression is of type character varying
  建议：You will need to rewrite or cast the expression.
  位置：193
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "take_effect_start_time" is of type date but expression is of type character varying
  建议：You will need to rewrite or cast the expression.
  位置：193] with root cause
org.postgresql.util.PSQLException: ERROR: column "take_effect_start_time" is of type date but expression is of type character varying
  建议：You will need to rewrite or cast the expression.
  位置：193
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)

使用MyBatis向PostgreSQL数据库插入数据，数据库类型是date,入参是string，报错。

解决

spring:
  datasource:
    druid:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://127.0.0.1:5432/bd?stringtype=unspecified
      username: root
      password: root

在配置url地址后面增加 stringtype=unspecified

原因

在最初实现该功能时考虑过使用Java代码拼接成sql，这样能够根据查询到的数据表列的字段类型来动态拼接，也能有使用一些数据库函数，在遇到该问题的时候也想使用这种方式解决，但时间日期类型的字符串格式多种多样，在使用一些数据库函数将字符串转成日期时需要指定其格式，这样的话就需要对日期字符串做各种格式的校验和转换。
stringtype=unspecified即自动格式化数据。