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!

http://stackoverflow.com/questions/3959692/rownum-in-postgresql — ZaoTaoBao, Jul 29 '15 at 07:24
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, Dec 15 '15 at 08:30

do-not-forsake-me · Answer 1 · 2015-07-29T07:45:16.980

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 '15 at 07:45

answered Jul 29 '15 at 07:18

do-not-forsake-me

21
5

1

Although note window functions are not allowed in the WHERE clause, as OP wants to do – harmic Jul 29 '15 at 07:22
@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. – Jul 29 '15 at 07:57
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. – harmic Jul 29 '15 at 10:05
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. – Catalin Vladu Jul 30 '15 at 10:09

score 1 · Answer 2 · answered Jul 29 '15 at 07:30

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.

Converting rownum from Oracle to Postgres

2 Answers2