How can I insert null in column NULL for the type TIME WITHOUT TIME ZONE

Question

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?

Answer 1

Your INSERT command works ok when values supplied are compatible.
The error you are geting implicates that you are trying to cast BYTEA datatype (byte array) as TIME datatype. Check the values passed to INSERT command.

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 (1, 
        1, 
        CAST('01:00:00' as TIME), 
        CAST('02:00:00' as TIME), 
        CASE WHEN 1=2 
             THEN CAST('03:00:00' AS TIME) 
        ELSE CAST(NULL AS TIME) 
        END, 
        CAST('01:00:00' as TIME),
        100
       )

Select * From 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
1	1	01:00:00	02:00:00	null	01:00:00	100

fiddle