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, Apr 18 '13 at 06: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, ...] — , Apr 18 '13 at 07:21
When I execute the query in my sql editor everything works fine. I think this guy here has the same problem: http://www.postgresql.org/message-id/alpine.BSO.2.00.1103251055350.9510@leary.csoft.net 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. — , Apr 18 '13 at 07:35
Is it possible that the provider tries to insert "null" as a string literal? — , Apr 18 '13 at 07:43
Sven, please edit your question to add the *full*, *unedited* statement and stack trace. — Craig Ringer, Apr 18 '13 at 08:05
Looks like you have a data type mismatch there. The error reads *column "packagesdeletetime" has type timestamp without time zone, but the expression is of type character varying* . I'm suspicious that you may well have encountered a provider bug; please make sure you're using the latest PgJDBC and the latest version of your JPA provider. The statement log from your JPA provider unfortunately fails to differentiate between strings and numbers, so you might want to set `log_statement = 'all'` in `postgresql.conf` and get the statement from the Pg logs instead. — Craig Ringer, Apr 19 '13 at 06:31

score 1 · Answer 1 · answered Apr 18 '13 at 05:21

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 '13 at 05:21

Eelke

20,897
4
50
76

Thanks for your answer. I try it but nullable=true is the default setting. – Apr 18 '13 at 05:28

Cannot insert NULL values into database

1 Answers1