auto generated unique ID within different groups

Question

I am assigning an auto generated PK for the id but in the same table I want to create another column which is unique (and auto generated) for each company. This helps each company to have much ordered and meaningful numbers in their id column. (I am using pgadmin and postgres)

Any help is really appreciated.

Answer 1

Use row_number():

select t.*,
    row_number() over(partition by company order by id) as company_id
from mytable t