Postgres sequencing composite key

Question

I have table_a with column product_id, store_id, x.

I want to

create table_b as
select store_id, product_id, sequence_number
from table_a

sequence_number is auto generated number that is supposed to look like this:

store_id            |   product_id              |   sequence_number
1                   |   1                       |   1
1                   |   1                       |   2
1                   |   1                       |   3
1                   |   2                       |   1
1                   |   2                       |   2
2                   |   1                       |   1
2                   |   1                       |   2

Is it possible to do this with sql query?

You are looking for row_number(): postgresql.org/docs/current/static/tutorial-window.html — user330315
– user330315, Commented Apr 4, 2016 at 10:23

Gordon Linoff · Accepted Answer · 2016-04-04 10:40:16Z

1

You can do this with row_number():

create table_b as
    select store_id, product_id,
           row_number() over (partition by store_id, product_id
                              order by NULL
                             ) as sequence_number
    from table_a;

answered Apr 4, 2016 at 10:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Yuka Fujisaku Over a year ago

Thanks! But what's the meaning of order by NULL?

Gordon Linoff Over a year ago

@YukaFujisaku . . . It is equivalent to leaving out the order by clause. However, I think leaving it out is a bit confusing, because most databases require it.

Collectives™ on Stack Overflow

Postgres sequencing composite key

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related