英文:
NULL value for LocalDateTime JPA 2.2 - PostgreSQL
问题
无法为下面描述的字段设置空值。我想将NULL插入为默认值。
@Column(name = "answer_date", columnDefinition = "TIMESTAMP")
private LocalDateTime answerDate
当我设置为NULL时，会显示以下错误：
Caused by: org.postgresql.util.PSQLException: ERROR: column "answer_date" is of type timestamp without time zone but expression is of type character varying
我正在使用EclipseLink作为JPA实现。
英文:
I am not able to set the null value for the field described below. I want to insert NULL as the default value.
@Column(name = "answer_date",columnDefinition = "TIMESTAMP")
private LocalDateTime answerDate
When I set the NULL, it shows the following error:
Caused by: org.postgresql.util.PSQLException: ERROR: column "answer_date" is of type timestamp without time zone but expression is of type character varying
I am using EclipseLink as JPA implementation.
答案1
得分: 2
我认为这是一个已知问题。请参考以下链接：
- https://bugs.eclipse.org/bugs/show_bug.cgi?id=535431 - 提出的错误
- https://bugs.eclipse.org/bugs/show_bug.cgi?id=546312 - 错误已修复
- https://www.postgresql.org/messageid/200412101610.16959.scrawford%40pinpointresearch.com
- https://github.com/typeorm/typeorm/issues/2290
（typeorm 也面临着相同的问题）。
> 是的，错误消息来自于 PostgreSQL JDBC 驱动程序。导致这个错误的原因是 eclipselink 将 varchar 作为 'null' 值的数据类型。其他驱动程序可能会忽略这个错误的数据类型，而 PostgreSQL 驱动程序则不会。
当您尝试将 answerDate 设置为 null 时，它会变成一个 "null" 字符串，该字符串会被提交到 PostgreSQL 数据库，由于 "null" 不是有效的时间戳格式，从而引发此错误。
最好在 LocalDateTime 不存在的情况下分配一个默认值。
private LocalDateTime answerDate = LocalDateTime.now();
在这种情况下，可以避免出现"is of type timestamp ... but
expression is of type text.." 的错误消息。
更新
这个错误在 eclipseLink 版本 2.7.6 中已修复（https://github.com/eclipse-ee4j/eclipselink/pull/415）。
英文:
I think this is a known issue. Refer links
- https://bugs.eclipse.org/bugs/show_bug.cgi?id=535431 - Bug raised
- https://bugs.eclipse.org/bugs/show_bug.cgi?id=546312 - Bug Fixed
- https://www.postgresql.org/messageid/200412101610.16959.scrawford%40pinpointresearch.com
- https://github.com/typeorm/typeorm/issues/2290
(typeorm also faces the same issue).
> Yes the error message comes from the postgres jdbc driver. The reason
> for this error is eclipselink using varchar as datatype for the 'null'
> value. Other drivers might or might not ignore this wrong datatype,
> the postgres driver does not.
When you try to set the answerDate to null, it turns into a "null" string which is submitted to the postgres database and causes this error since "null" is not a valid timestamp format.
Its better if you assign a default value in case the LocalDateTime is not present.
private LocalDateTime answerDate = LocalDateTime.now();
In this case, this error message saying "is of type timestamp ... but
expression is of type text.." can be avoided.
UPDATE
The error is fixed in eclipseLink version 2.7.6 (https://github.com/eclipse-ee4j/eclipselink/pull/415)
答案2
得分: 0
也许对某人有用。
我遇到了相同的错误（我使用了EclipseLink，Websphere和PostgreSQL）。
在将EclipseLink更新到2.7.8之后，我仍然继续收到相同的错误。
经过几个小时的搜索，我找到了以下的解决方案：
在server.xml中的dataSource中添加属性：
stringType="unspecified"
或者将stringType=unspecified作为查询参数添加到Postgres URL中。
英文:
Maybe it will be useful for somebody.
I had the same error (I used EclipseLink, Websphere and PostgreSQL).
After update EclipseLink to 2.7.8 I have continued to get the same error.
After the several hours of search I found the following solution:
Add property to dataSource in server.xml
stringType="unspecified"
or set stringType=unspecified as query parameter to Postgres URL
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论