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

Postgresql – Could not interpret path expression 'rownum'

I have this Oracle query implemented as HQL and Spring Boot query which I want to migrate to PostgreSQL

  @Query("select profileId from HrDepartment where profileId = :id and profileId is not null and rownum = 1)")
  String getProfileId(@Param("id") String id);

What is the proper way to use migrate rownum to PostgreSQL?

EDIT:

The solution so far that I found is

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null)")
      String getProfileId(@Param("id") String id, Pageable pageable);

repository.getProfileId("somId), PageRequest.of(0, 1));

Is there some better solution?

Tags:

2

Answers


  1. 0

    In PostgreSQL, you can achieve similar functionality to Oracle’s rownum using the LIMIT clause combined with OFFSET

    @Query("SELECT profileId FROM HrDepartment WHERE profileId = :id AND profileId IS NOT NULL LIMIT 1")
    String getProfileId(@Param("id") String id);

    In this PostgreSQL version, LIMIT 1 ensures that only one row is returned, and there’s no need for an equivalent of rownum = 1 in PostgreSQL.

    Just make sure that your PostgreSQL database schema matches the table and column names used in the HQL query, and ensure that the HrDepartment entity is mapped correctly in your Spring Boot application.

    Login or Signup to reply.
  2. 0 
    In PostgreSQL, you can achieve similar functionality to Oracle's rownum using the LIMIT clause combined with OFFSET. However, since rownum in Oracle returns the first n rows that satisfy the condition, you need to use both LIMIT and OFFSET to mimic this behavior.

select profileId 
from HrDepartment 
where profileId = :id and profileId is not null 
limit 1 offset 0

This query will return the first row that satisfies the conditions specified, which is essentially what rownum = 1 does in Oracle.

In your Spring Data JPA repository, you can modify the method like this:

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null")
String getProfileId(@Param("id") String id, Pageable pageable);

And then when calling this method, you can create a PageRequest with PageRequest.of(0, 1) to mimic the behavior of selecting only the first row:

repository.getProfileId("someId", PageRequest.of(0, 1));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search