Mastering PostgreSQL 9.6

row_number() function

The last function discussed in this section is row_number() function, which can simply be used to return a virtual ID. Sounds simple? 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) 

row_number() function simply assigns a number to the row. There are definitely no duplicates.

The interesting point here is that this can even be done 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  ---------+------------+------------ ...