In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus the corresponding Account id. But I would also like to assign each one an Account-specific id – just a number starting at 1 for each Account's first Customer and incrementing for each additional Customer – simply as an easy way for users in an Account to reference a Customer in the UI.
I can think of two possible solutions:
-
Use the
max()
aggregate function to obtain the last Customer id used for the Account and increment it- Pros
- Relatively simple (conceptually, at least)
- Cons
- Can result in an id being reused if the newest Customer is deleted
- Need to handle concurrent inserts calculating the same id for the new records
- Pros
-
Use a
sequence
- Pros
- Specifically designed to handle incrementing the value
- Remembers the last-used value, even if the corresponding record is deleted
- Cons
- Requires a separate
sequence
to be created for each new Account somehow (and then be deleted if/when the Account is deleted), otherwise each Account would end up with non-contiguous values in its Customer id sequence
- Requires a separate
- Pros
UPDATE
To clarify the last point, some gaps in the sequence of ids are always going to occur; as soon as a Customer is deleted, for example, but those are to be expected. Aside from that though, a user adding a couple of new Customers would generally expect them to have consecutive ids even if they were added days apart.
OTOH, using a single sequence
for all Customers could almost certainly see regular, unexplained gaps introduced by the ids assigned to the Customers created for other Accounts in the meantime.
I'm not a Postgres expert, so I'm concerned about how robustly I could implement either of these, especially as I've struggled to find any examples to follow.
Is there a generally accepted way of doing this?
Best Answer
How about using a column in the table
account
calledmax_customer_id
and updating that every time there's a new customer? That should be the simplest solution available that I can think of.With this solution you have to take care of the concurrency. Take a look at the document PostgreSQL Concurrency issues:
Alternatively you could place the value in a separate table. This table would contain the
account_id
and maximumaccount_specific_id
in use for that account. That way you could handle the concurrency easier withUPDATE
s. Same source:The number 2 option with sequences is not however recommended in this case as new sequences are not meant to be routinely inserted in a production environment.