Home > Documentation > Oracle to PostgreSQL

Migration of ROWNUM from Oracle to PostgreSQL

One of common challenges while migrating from Oracle to PostgreSQL is proper conversion of ROWNUM that does not have direct equivalent in the target database management system. Oracle assigns the ROWNUM to each row of recordset created as result of some query. It is an increasing sequence of integer numbers starting at 1 with step 1.

Since ROWNUM values are assigned to the entire row, multiple tables combined in a single rowset through JOIN-statement provide single ROWNUM for every record. If the order rowset is changed, the association of the ROWNUM with the data will be changed also.

In Oracle ROWNUM feature is mostly used to limit number of rows in query results, for example:

SELECT * FROM table1 WHERE ROWNUM <= 5

Obviously, this query returns the first 5 rows of query result in random order. PostgreSQL equivalent of that query is:

SELECT * FROM table1 LIMIT 5

Another common reason of using Oracle ROWNUM is to filter and sort results of subquery, for example:

SELECT ROWNUM, a.* FROM (
	SELECT employees.* 
	FROM employees WHERE id > 500 
	ORDER BY last_name
) a;

The most straight forward approach to implementation of the same query in PostgreSQL is to create a sequence and then select its values on the fly:

CREATE SEQUENCE seq_employees_iterator;
SELECT nextval('seq_employees_iterator') rownum, * FROM (
	SELECT employees.* 
	FROM employees WHERE id > 500 
	ORDER BY last_name
) a;
DROP SEQUENCE seq_employees_iterator;

Although this method may work for simple queries, it has multiple important limitations:

• This implementation of rownum is not accessible to the WHERE clause
• This implementation of rownum cannot be used in a LIMIT clause
• It is necessary to check that sequence name is available and to destroy temporary sequence when it is not required anymore

Better implementation of ROWNUM is based on PostgreSQL window function ROW_NUMBER() that assigns a sequential integer to each row in a result set:

SELECT rownum, * FROM (
	SELECT row_number() OVER () rownum, employees.* 
	FROM employees WHERE id > 500 
	ORDER BY last_name
) a;

This approach allows to obtain the rownum column that may be used with WHERE and LIMIT clauses. It does not require creating any supplementary database objects that also must be destroyed in the proper time.

More information about how to migrate Oracle queries to PostgreSQL is available in this whitepaper