[SOLVED] How to create sequential ID's per user in Postgresql

How to create sequential ID's per user in Postgresql

I'm trying to create a table in postgresql to hold user Work Items. Each Work Item will have a unique ID across the system, but then will also have a friendly sequential ID for that specific user.

So far I have been just using:

CREATE TABLE WorkItems
(
  Id SERIAL NOT NULL PRIMARY KEY,
  ...
);

but I don't know how to compartmentalize this serial per user, nor how to do it with sequences either.

So I want for users to see sequential Friendly Ids

User1: ids = 1, 2, 3 etc..
User2: ids = 1, 2, 3, 4, 5 etc..

and not have to deal with the unique Ids, but those items would have unique ids from 1-8.

I've looked around quite a bit and can't find any posts on this topic. Maybe it's just hard to specify and search for?

Should I be storing the LastWorkIdUsed in a user column, and then just manually create a friendly Id for their items? Seems like I'd unnecessarily have to worry about concurrency and transactions then. Is there an easy way to pass the LastWorkIdUsed per user to Postgresql when generating a number?

Or is there a way to assign a sequence to a user?

Perhaps I've got the fundamental design idea wrong. How would one do this in SQLServer or MySQL also?

Solution

You said that you also have a globally unique key. You can use that to solve your concurrency issues. If you allow the user work item number to be null, you can first create the work item, then do something like this to assign it a "friendly" number. (T-SQL dialect, @workItemID is the globally unique ID):

Update wi
set friendlyNumber = coalesce(
    (select max(friendlyNumber)+1 from WorkItem wi2 
     where wi2.userID = wi.userID and not wi2.friendlyNumber is null)
, 1)
From WorkItem wi
where workItemID = @workItemID and friendlyNumber is null

Then find out the number like this:

select friendlyNumber from WorkItem where workItemID = @workItemID