Mastering PostgreSQL 12 - Third Edition

The row_number() function

The last function we will discuss in this section is the row_number() function, which can simply be used to return a virtual ID. Sounds simple, doesn't it? Here it is:

test=# SELECT country, production,               row_number() OVER (ORDER BY production)        FROM   t_oil        LIMIT 3;  country | production | row_number 
---------+------------+------------  Yemen   |         10 |         1 Syria   |         21 |         2 Yemen   |         26 |         3(3 rows)

The row_number() function simply assigns a number to the row. There are definitely no duplicates. The interesting point here is that this can be done even without an order (in case it is not relevant to you):

test=# SELECT country, production,               row_number() OVER()        FROM   t_oil        LIMIT 3;  country | production | row_number---------+------------+------------ ...