I have table with column ‘additional_session_end_ts’ which has type TIME WITHOUT TIME ZONE and in this column there can be null values.
I have simple method in repository in my Java application:
@Modifying
@Query(value = "INSERT INTO auction_proposal_list_link (auction_proposal_id, auction_list_id, mini_session_start_time, " +
"mini_session_end_time, additional_session_end_ts, mini_session_start_time_fixed, mini_session_number) " +
"VALUES (:auctionProposalId, :auctionListId, :sessionStartTime, :sessionEndTime, " +
"CASE WHEN :additionalSessionEndTime IS NOT NULL THEN CAST(:additionalSessionEndTime AS time) ELSE CAST(NULL AS time) END, " +
":startTimeFixed, :miniSessionNumber)",
nativeQuery = true)
void insertAuctionBySeries(
@Param("auctionProposalId") Long auctionProposalId,
@Param("auctionListId") Long auctionListId,
@Param("sessionStartTime") LocalTime sessionStartTime,
@Param("sessionEndTime") LocalTime sessionEndTime,
@Param("additionalSessionEndTime") LocalTime additionalSessionEndTime,
@Param("startTimeFixed") Boolean startTimeFixed,
@Param("miniSessionNumber") Long miniSessionNumber
);
and I always see an error:
InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statementcaused by: SQLGrammarException: could not execute statement caused by: PSQLException: ERROR: cannot cast type bytea to time without time zone
And this error happened because I’ve tried to insert in additional_session_end_ts NULL.
I’ve tried different approaches like above, COALESCE(CAST(:additionalSessionEndTime AS time), NULL::time), just insert null and nothing works for me.
I just want to have the simple logic – insert some value in additional_session_end_ts column or insert NULL if value is null.
How can I do it?