Create custom sequence generator in Postgres to consider column value

Question

I have a table called jobs in Postgres, which has columns job_id, username, created_ts and other job related informations. Column job_id is of serial type.

...
job_id serial NOT NULL,
username character varying(32),
...

username denotes the user who created the job. An user can only see the jobs which are created by him. He can see the job_id also. This makes the user to see random job_ids, not strictly sequence.

I want to make the sequence start from 1 for each user. It has to be sequential with respect to username. This will create duplicate job_ids but that's okay.

I have referred https://www.postgresql.org/docs/9.5/static/sql-createsequence.html I couldn't find any way to create a sequence by column value. Is it possible to create such sequence generator in Postgres?

404 · Accepted Answer · 2018-03-15 17:27:49Z

1

I don't know if you can do that with sequences, but unless someone can think of something better, you could remove the sequence and instead have a trigger on INSERT that does something like

NEW.job_id := COALESCE((SELECT MAX(job_id) FROM jobs WHERE username = NEW.username), 0) + 1;

As long as you have the appropriate indexes it should be perfectly fast. Though I can foresee issues with concurrency. Depends how you expect to use it.

answered Mar 15, 2018 at 17:27

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create custom sequence generator in Postgres to consider column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related