column is of type date but expression is of type bytea

Question

I want to implement a custom column handler in Hibernate/Spring Boot app. I tries this:

Entity:

@Entity
@Table(name = "single_user")
@Getter
@Setter
public class SingleUser {

    @Id
    @SequenceGenerator(name = "su_seq", sequenceName = "su_seq", allocationSize = 100)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "su_seq")
    @Column(name = "id", nullable = false)
    private Long id;

    @Convert(converter = ExpiryDateTypeHandler.class)
    @Column(name = "expiry_date")
    private ExpiryDate expiryDate;
 
    // getter, setter
}

Spring JPA repository:

SingleUserDao entity;
singleUserRepository.create(entity.getId(),
                            .....,
                                    Optional.ofNullable(entity.getRecipient()).map(CalendarDate::getYear).orElse(0),

I get error:

   [ERROR: column "card_expiry_date" is of type date but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 215] [n/a]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: column "card_expiry_date" is of type date but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

Basic reproducible code: https://github.com/rcbandit111/JdbcTypePoc.git

Do you know how I can implement the column AttributeConverter properly when I insert new line?

ozkanpakdil · Accepted Answer · 2025-08-25 19:55:17Z

JPA AttributeConverter applies when Hibernate/JPA manages the persistence of an entity attribute (e.g., via EntityManager.persist or JpaRepository.save). AttributeConverter does NOT apply to parameters you pass into a native SQL query. When you pass a custom object (ExpiryDate) as a native query parameter, Hibernate/JDBC doesn’t know how to bind it to a SQL DATE, so it tries to bind the object as a binary (bytea) parameter in PostgreSQL, causing the type mismatch.

@Converter(autoApply = true)
public class CardExpiryDateTypeHandler implements AttributeConverter<ExpiryDate, Date> {
...

With @Converter(autoApply = true), you don’t need @Convert on every field; otherwise keep the explicit @Convert.

As result you can have repository like below, no need to write native sql

@Transactional
@Repository
public interface SingleUseTokenRepository extends JpaRepository<SingleUseTokens, Long> {
}

and in DAO implementation you can have create like below

public void create(SingleUseParentToken entity) {
  ExpiryDate expiryDate = new ExpiryDate();
  expiryDate.setMonth(2);
  expiryDate.setYear(2018);

  SingleUseTokens sut = new SingleUseTokens();
  sut.setCardExpiryDate(expiryDate);

  singleUseTokenRepository.save(sut);
}

If you must use native SQL: bind a JDBC date type, not your value object

AttributeConverters are not applied to native query parameters.
Convert your ExpiryDate to java.sql.Date/LocalDate before binding.

Collectives™ on Stack Overflow

column is of type date but expression is of type bytea

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related