Postgres generate_series for each unique row

Question

I'm trying to do something like this

key | series
____________
  1 |   1
  1 |   2
  2 |   1
  3 |   1
  3 |   2
  3 |   3
  4 |   1
  4 |   2

How do I go about generating a series for each unique key?

krokodilko · Accepted Answer · 2014-01-02 22:29:03Z

1

Use a window function ROW_NUMBER():

SELECT key, 
       row_number() over (partition by key) series
FROM table1
;

answered Jan 2, 2014 at 22:29

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

1 Comment

So awesome. Simple and elegant. Worked perfectly for me.