Oracle to Postgres Migration (Top Row with Order by)-CodePudding

Migrating a code from Oracle to Postgres:

Oracle Code:

SELECT *
FROM (SELECT customers.*
      FROM customers
      WHERE customer_id > 4500
      ORDER BY last_name)
WHERE ROWNUM < 3;

To the best of my knowledge, it's Oracle's limitation that we need to add a subquery to get a proper sorted result if have to limit the output with rownum.

Do Postgres also carry the same limitations or can I convert the code in Posgtes into a single query as under?

SELECT customers.*
      FROM customers
      WHERE customer_id > 4500
      ORDER BY last_name
      LIMIT 3;

CodePudding user response：

Your assumption is correct and simple to test:

drop table if exists test_70069841;
create table test_70069841 as select 2;
insert into test_70069841 select 3;
insert into test_70069841 select 5;
insert into test_70069841 select 6;
insert into test_70069841 select 7;
insert into test_70069841 select 1;
select * from test_70069841;
-- ?column?
------------
--        2
--        3
--        5
--        6
--        7
--        1
--(6 rows)

select * from test_70069841 order by 1;
-- ?column?
------------
--        1
--        2
--        3
--        5
--        6
--        7
--(6 rows)

select * from test_70069841 order by 1 limit 3;
-- ?column?
------------
--        1
--        2
--        3