ROWNUM Pseudo-column - Oracle to PostgreSQL Migration

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 */

ROWNUM to Generate Row Numbers

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

ROWNUM and Expressions

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.