Question 356861 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – <expression> expected, got ':'

I have the following Oracle SQL query:

SELECT user FROM global_users user WHERE user.status = 'ACTIVE'
AND user.description IS NOT NULL AND user.updatedGoodsDate BETWEEN '2024-03-10 20:09:53' AND '2024-03-10 20:09:53'
AND ROWNUM <= 13

I tried to edit the query into Postgres with Spring Data JPA:

@Query("SELECT user FROM global_users user WHERE user.status = :status
AND user.description IS NOT NULL AND user.updatedGoodsDate BETWEEN :startDate AND :endDate
AND LIMIT :count")
List<Users> findTopUsers(@Param("status") TransactionStatus status,
  @Param("startDate") OffsetDateTime start, @Param("endDate") OffsetDateTime end, @Param("count") long count);

But for LIMIT :count I get <expression> expected, got ':'. Do you know how I can fix this?

Tags:

2

Answers


  1. 0

    I believe JPA does not support adding parameters for LIMIT.

    You should be able to use Pageable to limit your result set, as shown in this answer: JPA limit functionality

    In your case, this would look like:

    @Query("SELECT user FROM global_users user WHERE user.status = :status
    AND user.description IS NOT NULL AND user.updatedGoodsDate BETWEEN :startDate AND :endDate)

    List findTopUsers(@Param("status") TransactionStatus status,
    @Param("startDate") OffsetDateTime start, @Param("endDate") OffsetDateTime end, Pageable pageable);

    Login or Signup to reply.
  2. 0

    Starting from SpringDataJPA 3.2 is possible to use Limit type.

    As described here https://www.baeldung.com/jpa-limit-query-results#3-using-limit :

    Spring Data JPA version 3.2 provides a new type named Limit to limit
    the number of results returned from a query. It has two static methods
    – of() and unlimited().

    The of() method accepts an integer as an argument. It limits query
    results to the given maximum size. This allows fetching only the
    specified number of results.

    Next, the unlimited() method returns all the matching results from a
    query. it doesn’t restrict the number of results.

    Let’s use the of() method from Limit to limit the query result to 1:

    List<Passenger> passenger = repository.findByOrderBySeatNumberAsc(Limit.of(1));

    Here, we invoke the static method of() on Limit to return 1 result
    based on our specification.

    Official documentation of Spring here: https://docs.spring.io/spring-data/jpa/reference/repositories/query-methods-details.html#repositories.special-parameters

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search