In Oracle, the ROWNUM pseudo-column returns the order number of rows selected from a table. It is usually used to limit the number of rows returned by a query, but ROWNUM can be also used to generate row numbers. Note that ROWNUM is applied before ORDER BY.
In PostgreSQL, you can use the LIMIT clause and ROW_NUMBER() OVER () function.
Consider a sample table:
CREATE TABLE colors (name VARCHAR(30)); INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('Black'); INSERT INTO colors VALUES ('Red');
Oracle:
-- Select 1 row only SELECT name FROM colors WHERE rownum = 1; /* Green */ -- Select 2 rows SELECT name FROM colors WHERE rownum <= 2; SELECT name FROM colors WHERE rownum < 3; /* Green */ /* Black */
PostgreSQL:
-- Select 1 row only SELECT name FROM colors LIMIT 1; /* Green */ -- Select 2 rows SELECT name FROM colors LIMIT 2; /* Green */ /* Black */
If the ROWNUM pseudo-column is used in the SELECT expression it generates the row numbers:
Oracle:
-- Generate row numbers SELECT ROWNUM as RN, name FROM colors;
Result:
rn | name |
1 | Green |
2 | Black |
3 | Red |
But you can see that ROWNUM is applied before ORDER BY clause:
Oracle:
-- Generate row numbers with ORDER BY SELECT ROWNUM as RN, name FROM colors ORDER BY name;
Result:
rn | name |
2 | Black |
1 | Green |
3 | Red |
In PostgreSQL, you can use ROW_NUMBER() OVER() function:
PostgreSQL:
-- Generate row numbers SELECT ROW_NUMBER() OVER () as RN, name FROM colors;
Result:
rn | name |
1 | Green |
2 | Black |
3 | Red |
Note that similar to Oracle ROWNUM, ROW_NUMBER() OVER () is also applied before the ORDER BY clause of the SELECT statement:
PostgreSQL:
-- Generate row numbers with ORDER BY SELECT ROW_NUMBER() OVER () as RN, name FROM colors ORDER BY name;
Result:
rn | name |
2 | Black |
1 | Green |
3 | Red |
In Oracle, you can specify any expression for ROWNUM:
Oracle:
-- Specifying expression SELECT * FROM colors WHERE rownum <= CAST(SUBSTR('12', 1, 1) AS INT); /* Ok */
PostgreSQL also allows you to specify any expression in the LIMIT clause:
PostgreSQL:
-- Specifying expression SELECT * FROM colors LIMIT CAST(SUBSTR('12', 1, 1) AS INT); /* Ok */
For more information, see Oracle to PostgreSQL Migration.