ROW_NUMBER Function - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the ROW_NUMBER function is a window function that numbers the output rows.

Consider the following sample table:

  CREATE TABLE colors (name VARCHAR(30), category CHAR(1));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green', 'A');
  INSERT INTO colors VALUES ('White', 'B');
  INSERT INTO colors VALUES ('Black', 'A');
  INSERT INTO colors VALUES ('Red', 'B');

SQL Server and PostgreSQL:

  -- Number rows
  SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS num FROM colors;

Result:

name	num
Black	1
Green	2
Red	3
White	4

SQL Server and PostgreSQL:

  -- Number rows within group
  SELECT name, ROW_NUMBER() OVER (PARTITION BY category ORDER BY name) AS num FROM colors;

Result:

name	num
Black	1
Green	2
Red	1
White	2

For more information, see SQL Server to PostgreSQL Migration.