PostgreSQL – Independent Serial for Each Foreign Key Value

postgresql

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.

Best Answer

You can't do this with SERIAL.

You could create a SEQUENCE for each owner_id but that'd be painful and scale poorly.

Instead, use a counter table where each owner_id has a separate counter, and 

update players_per_user_counter 
set next_val = next_val + 1 
where player_id = $1 
returning next_val

to get new identifiers. For more info search for "PostgreSQL gapless sequence".

See:

Related Question