NULL value for LocalDateTime JPA 2.2 - PostgreSQL

Question

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.

Answer 1

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)

Answer 2

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