Cannot insert NULL values into database

Question

We use JPA to load/persist our entities in a prostges database. In one case i must persist a entity with a native query. Heres is the code:

String sql = "INSERT INTO recording (id,creationdate,duration,endtime,filemaninfocomplete,lastupdated,packagesdeletetime,rcinfocomplete,"
                + "rcrecordingkept,recordstate,recordingtype,starttime,tenantid,recordingnode_id,transcription_id) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, recording.getId());
query.setParameter(2, new Date(), TemporalType.TIMESTAMP);
query.setParameter(3, recording.getDuration());
query.setParameter(4, recording.getEndtime(), TemporalType.TIMESTAMP);
query.setParameter(5, recording.isFileManInfoComplete());
query.setParameter(6, new Date(), TemporalType.TIMESTAMP);
query.setParameter(7, recording.getPackagesDeleteTime(), TemporalType.TIMESTAMP);
query.setParameter(8, recording.isRcInfoComplete());
query.setParameter(9, recording.isRcRecordingKept());
query.setParameter(10,recording.getRecordState() != null ? recording.getRecordState().toString() : RecordState.DEFAULT);
query.setParameter(11,recording.getRecordingType()!= null ? recording.getRecordingType().toString() : null);
query.setParameter(12,recording.getStarttime(), TemporalType.TIMESTAMP);        
query.setParameter(13,recording.getTenantId());        
query.setParameter(14,recording.getRecordingNode() != null ? recording.getRecordingNode().getId() : null);        
query.setParameter(15, recording.getTranscription() != null ? recording.getTranscription().getId() : null);        
query.executeUpdate();

The insert works finde unless "getPackagesDeleteTime" returns null. In this case the following message ist shown:

Caused by: org.postgresql.util.PSQLException: FEHLER: Spalte »packagesdeletetime« hat Typ timestamp without time zone, aber der Ausdruck hat Typ character varying
  Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen.
  Position: 252

(ie:

ERROR: column "packagesdeletetime" has type timestamp without time zone, but the expression is of type character varying
Hint: You must rewrite the expression or typecast.

)

Generated Statement looks like:

INSERT INTO recording(id,creationdate,duration,endtime,filemaninfocomplete,lastupdated,packagesdeletetime,rcinfocomplete,rcrecordingkept,recordstate,recordingtype,starttime,tenantid,recordingnode_id,transcription_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
bind => [0c850391-cae9-4ac2-9381-b252167fa239, 2013-04-18 08:38:00.704, 20240, 2013-04-18 08:37:24.131, false, 2013-04-18 08:38:00.704, null, false, true, DEFAULT, VOICE, 2013-04-18 08:37:03.891, f56251a7-44df-4151-aa0d-7c3fc538f621, null, null]

Some tips how i could solve the problem?

Please show the full stack trace, the JPA provider and provider version, the full exact text of the error from PostgreSQL (see the Pg error log if it isn't in the stack trace) and the SQL that the JPA provider generated. — Craig Ringer
– Craig Ringer, Commented Apr 18, 2013 at 6:37
nullable true doesn´t work. Heres the stacktrace: Internal Exception: org.postgresql.util.PSQLException: FEHLER: Spalte »packagesdeletetime« hat Typ timestamp without time zone, aber der Ausdruck hat Typ character varying Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen. and the generated Insert Statement: INSERT INTO recording (...,packagesdeletetime,...) VALUES (...,?,...) bind => [..., null, ...] — user652158
– user652158, Commented Apr 18, 2013 at 7:21
When I execute the query in my sql editor everything works fine. I think this guy here has the same problem: postgresql.org/message-id/… Entity mapping is ok. Database says it is ok (when inserting with query tool). The problem occurs only on timestamp fields when i try to insert null values. We use eclipselink as provider. — user652158
– user652158, Commented Apr 18, 2013 at 7:35
Is it possible that the provider tries to insert "null" as a string literal? — user652158
– user652158, Commented Apr 18, 2013 at 7:43
Sven, please edit your question to add the full, unedited statement and stack trace. — Craig Ringer
– Craig Ringer, Commented Apr 18, 2013 at 8:05

Eelke · Accepted Answer · 2013-04-18 05:21:34Z

1

The corresponding field in the entity has to be annotated with:

@Column(nullable=true)

Also check the corresponding column in your table allows NULL (has no NOT NULL constraint). If this doesn't help please post the code of your entity.

answered Apr 18, 2013 at 5:21

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user652158 Over a year ago

Thanks for your answer. I try it but nullable=true is the default setting.

Collectives™ on Stack Overflow

Cannot insert NULL values into database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related