Migrate Oracle SQL query with ROWNUM into PostgreSQL HQL query

Question

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")
    List<Users> findTopUsers(@Param("status") TransactionStatus status,
      @Param("startDate") OffsetDateTime start, @Param("endDate") OffsetDateTime end, @Param("count") long count);

But I can't use LIMIT clause in Postgre. Is there some way to edit the query and get the same result?

For example can this be implemented with a subquery?

Test project: https://github.com/rcbandit111/oracle_rownum_pg_migration_poc/blob/main/src/main/java/com/test/portal/account/platform/repository/DataRepository.java

Answer 1

From Oracle 12 and, at least PostgreSQL 9, both RDBMS support the FETCH FIRST n ROWS ONLY syntax:

SELECT "USER"
FROM   global_users
WHERE  status = 'ACTIVE'
AND    description IS NOT NULL
AND    updatedGoodsDate BETWEEN TIMESTAMP '2024-03-10 20:09:53'
                            AND TIMESTAMP '2024-03-10 20:09:53'
-- ORDER BY something
FETCH FIRST 13 ROWS ONLY

Which, for the sample data:

CREATE TABLE global_users (
  "USER" VARCHAR(10),
  status VARCHAR(8),
  description VARCHAR(20),
  updatedGoodsDate TIMESTAMP
)

INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Alice', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Beryl', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Carol', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Debra', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Emily', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Fiona', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Grace', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Haley', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Irene', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Julie', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Kelly', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Lydia', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Megan', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');
INSERT INTO global_users ("USER", status, description, updatedGoodsDate)
VALUES ('Naomi', 'ACTIVE', 'X', TIMESTAMP '2024-03-10 20:09:53');

Outputs:

USER
Alice
Beryl
Carol
Debra
Emily
Fiona
Grace
Haley
Irene
Julie
Kelly
Lydia
Megan

Oracle fiddle PostgreSQL fiddle