CODEWITHSUNDEEP
postgresqldatabase-sequence
matthew fabrie
matthew fabrie

Reputation: 103

Postgres sequence that resets once the id is different

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!

Upvotes: 0

Views: 155

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

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).

Upvotes: 1

Related Questions