SQLGrammarException: could not execute statement while trying to delete from db

I'm trying to solve a problem with statement deletion query. Now the implementation looks like this.

 @Transactional
public void deleteStatements(LocalDate expiryDate) {
    int deletedStatements = statementRepository.deleteByIdCreatedDateBefore(expiryDate);
    logger.info("Deleted {} statements.", deletedStatements);
}


@Query(value = "WITH deleted AS (DELETE FROM generated_statements WHERE created_date < :expiryDate RETURNING id) " +
        "SELECT count(*) FROM deleted;", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

Now I'm getting this error:
could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

I tried adding @Modifying annotation, then I tried removing it, tried with various combinations of @Transactional and @Modifying, still got various errors regarding this deletion like:
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet or Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
Now I'm really not sure what is the problem here.

Replace your delete query with this

@Modifying
@Query(value = "DELETE FROM generated_statements WHERE created_date < :expiryDate", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

or turn it directly into a JPQL/HQL query. No need for a nativeQuery. Example:

@Modifying
@Query(value = "DELETE FROM GeneratedStatement g WHERE g.createDate < :expiryDate")
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

By specifying an int return value, you will automatically get the number of deleted rows.

Considering all this complex logic is to get number of deleted rows you can try just:

<!-- language: lang-java -->

@Modifying
@Query("DELETE FROM generated_statements WHERE created_date < :expiryDate")
int deleteByIdCreatedDateBefore(LocalDate expiryDate);

or just using keywords(if your entity has createdDate attribute)

int deleteByCreatedDateBefore(LocalDate expiryDate);

@Modifying
@Query(value = "DELETE FROM generated_statements WHERE created_date < ?1", nativeQuery = true)
int deleteByIdCreatedDateBefore(LocalDate expiryDate);