I have a table shared by many different users called players and a table called users.
Each player has the primary key id (serial) and a foreign key called owner_id which matches to users.id.
In order for users to update their players in my database (through my API) they must specify the same id as appears in the players table (the ids are visible to them!).
Because it's shared, the player ids that one user creates might look like:
1
2
12
56
(with the gaps being the id's used by players created by other users).
How can I create a new column called local_id which will behave like an independent serial on each owner_id value?
So each user would see the local_id as perfectly incrementing every time they create a new player?
1
2
3
4
Thanks for your help.