Auto-increment column scoped to another column

Is there a way to have an auto-increment / sequence column that is scoped to another column? e.g. in the following table, I’d like the sequence column to be auto-incremented for the same parent_key, but set to 1 for a new parent_key, and auto-incremented thereafter.

+------------+----------+
| parent_key | sequence |
+------------+----------+
| a          |        1 |
| a          |        2 |
| a          |        3 |
| b          |        1 |
| b          |        2 |
+------------+----------+

An example of this is github issue numbers, they’re scoped to each repo. Obviously I can use (max(sequence) + 1) for a given parent_key, but: (a) I’m hoping there’s something simpler; and (b) I’m not sure how to make this work without causing a race condition; it’ll probably need to be done as a sub-query during insert, e.g. insert ... (select coalesce(max(sequence), 0) + 1 from mytable where parent_key = 'a').

In general no, but an inline workaround is at: https://stackoverflow.com/questions/52131270/postgresql-auto-increment-column-by-scope-of-another-column-value

Thanks @OvermindDL1. From the link you provided, and through a lot of googling as well, it sounds like there’s no straightforward approach to this problem.

Computing the sequence using ROW_NUMBER() seems very fragile to me, as it’s susceptible to sequence numbers changing if records are deleted, or new ones are inserted in between existing records (based on the ordering timestamp).

I think my best option is to rely on max(sequence) + 1, but it needs to be done in a concurrent-safe way.

1 Like

May be relevant: Dynamically Create Sequences

1 Like