Custom Sequence - Postgres

Question

Problem Statment:

We have multiple tables containing a column having the name "Code", it is an identity column. Our application is running by our multiple locations/clients, and they are using the same database. Due to this simultaneous access of a table, our identity columns are suffering from a gap for each client.

Let's take an example:

Client C1 creates a record and he gets the Code as '1', meanwhile, Client C2 creates a record and he gets the Code as '2' which is wrong. He should gets the Code as '1', this issue is causing because both clients are using the same table.

Possible Solution:

We can use triggers to maintain custom sequence but in that case, we have to maintain trigger for each table which is tedious alternatively, We can use sequence.

By taking of sequence is there any possibility that we make such a sequence that can take a function and this function can catch the table name internally without passing the table as a parameter.

Or

If there is any best solution that can solve this problem please recommend.

Thanks

Answer 1

gaps really doesn't matter, but simply use uuids instead of identity .

the queries will suffer performance but every client creates his own uuids and the are unique, so you can put them together at some later point and all connecting tables too. so that the data integrity is maintained.

the uuids can be create by the software or in the database