Get auto generated sequence in postgresql

Question

I retrieved some city names from a table but along with names I want to show an auto generated Id eg:

    name | id
-------------------
    New Delhi | 1
    Kanpur | 2
    Mumbai | 3

How could i achieve this in PostGreSql? I don't wan't to generate a sequence and use nextval('sequence_name').

query for retrieving names: select city_name as name from cities;

Vivek S. · Accepted Answer · 2015-10-17 06:29:10Z

1

You can use row_number() function in Postgres's window functions

As per documentation

row_number() - number of the current row within its partition, counting from 1

select city_name as name
      ,row_number() over() id 
from cities;

answered Oct 17, 2015 at 6:29

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1