Postgres sequence that resets once the id is different

Question

I am trying to make a Postgres sequence that will reset once the id of the item it is linked to changes, e.g:

ID      SEQUENCE_VALUE
1              1
2              1
1              2
1              3
2              2
3              1

I don't know PSQL or SQL in general very well and I can't find a similar question, any Help Is greatly appreciated!

Answer 1

Just use a normal sequence that does not reset and calculate the desired value in the query:

SELECT id,
       row_number() OVER (PARTITION BY id
                          ORDER BY seq_col)
          AS sequence_value
FROM mytable;

Here, seq_col is a column that is auto-generated from a sequence (an identity column).