Converting rownum from Oracle to Postgres

Question

I need to make a conversion from Oracle SQL to PostgreSQL.

  select * from table1 inner join table2 on table1.id = table2.table1Id
  where table1.col1 = 'TEST' 
  and rownum <=5
  order by table2.col1

If I delete and rownum <=5 and put at the end limit 5, there are differences between the 2 dialects. In Oracle, first are selected the 5 elements, and after that, they are sorted by table2.col1 .
In Postgres, first all the list is sorted, and AFTER there are selected the first 5 elements.

How can I obtain the same result in Postgres as in Oracle?

Thanks!

Since the result in Oracle is basically random - there is no reliable way to predict which 5 rows will be selected - it's probably impossible to ensure that a query returns the same results on both systems. — Erich Kitzmueller
– Erich Kitzmueller, Commented Dec 15, 2015 at 8:30

harmic · Accepted Answer · 2015-07-29 07:30:35Z

1

To get the behavior you desire, you can use a subquery like this:

SELECT * FROM (
    SELECT table1.col1 as t1col1, table2.col1 as t2col1 
    FROM table1 INNER JOIN table2 ON table1.id = table2.table1Id
    WHERE table1.col1 = 'TEST'
    LIMIT 5
) AS sub
ORDER BY t2col1;

I named the columns there because in your example both tables had a col1.

Note however that without any ordering on the inner query, the selection of 5 rows you get will be purely random and subject to change.

answered Jul 29, 2015 at 7:30

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

do-not-forsake-me · Accepted Answer · 2015-07-29 07:45:16Z

1

Depending on the version you are using, PostgreSQL 8.4 and above have Window functions. Window function ROW_NUMBER() is capable of implementing the functionality of Oracle pseudo column rownum.

select row_number() over() as rownum,* from table1 inner join table2 on table1.id = table2.table1Id where table1.col1 = 'TEST' and rownum <=5 order by table2.col1;

edited Jul 29, 2015 at 7:45

answered Jul 29, 2015 at 7:18

do-not-forsake-me

211 silver badge5 bronze badges

4 Comments

harmic Over a year ago

Although note window functions are not allowed in the WHERE clause, as OP wants to do

user330315 Over a year ago

@harmic: you can always put the select with the row_number() into a derived table and then use the column alias in the where clause.

harmic Over a year ago

As per a_horse_with_no_name's comment, if you want to use window functions like that you have to put them in a derived table (ie. subquery in FROM clause). It's not enough to just use an alias within the same query.

Catalin Vladu Over a year ago

Today I met another situation, with the same order by: if in "select" i put "table1.col1, table2.col2" , and not " * ", the subselect does not work in this case for Postgres, because table2.col1 is not in the columns selected.

Collectives™ on Stack Overflow

Converting rownum from Oracle to Postgres

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related