How to dynamically create monotonically increasing sequence in PostgreSQL?

Question

I have two domain entities: employee and department. DDL looks like this:

CREATE TABLE department(
  id SERIAL PRIMARY KEY
);

CREATE TABLE employee(
  id SERIAL PRIMARY KEY,
  department_id integer,
  index_in_department integer
);

So, main field is index_in_department. It is unique monotonically increasing number per each department.

For example, I have 2 departments and 6 employees:

department - id == 1
department - id == 2

employee - id == 1, department_id == 1, index_in_department == 1
employee - id == 2, department_id == 1, index_in_department == 2
employee - id == 3, department_id == 1, index_in_department == 3
employee - id == 4, department_id == 2, index_in_department == 1
employee - id == 5, department_id == 2, index_in_department == 2
employee - id == 6, department_id == 2, index_in_department == 3

Is there any way to generate automatically index_in_department in PostgreSQL?

I cannot generate it on application level, because of concurrent inserts by multiple threads. I know, that I can use Database locks (e.g. advisory lock), but I want more easy and friendly solution.

How to dynamically create monotonically increasing sequence in PostgreSQL?

0 Answers0