How to update a column with unique serial number depending on two different columns of the same rows in PostgreSQL?

Question

I have a list of records in the below format (example) produced by joining multiple tables from the database and where conditions:

Col1	Col2	Col3
100	200	1
100	201	1
100	202	1
100	203	1
101	204	1
101	205	1
102	206	1
102	207	1

What I would like is to update the values in Col3 in the above example based on the values in Col1 and Col2.

The idea is to loop through Col1 first and then another loop in Col2 and update the values in Col3 starting from 1 and increment by 1 per each iteration over Col2 record. This should be repeated again for the next iteration on Col1.

A sample output expected from the above method is:

Col1	Col2	Col3
100	200	1
100	201	2
100	202	3
100	203	4
101	204	1
101	205	2
102	206	1
102	207	2

The database used is postgres and I am quite new to the functions like cursor in postgres. If anyone have any insight on this to have an efficient way to workout this problem will be great.

Appreciate your help thanks.

Thanks

Answer 1

You can achieve this easily with row_number()over() ranking window function:

Schema and insert statement:

 create table table1(Col1 int,  Col2    int,Col3 int);
 insert into table1 values(100  ,200    ,1);
 insert into table1 values(100  ,201    ,1);
 insert into table1 values(100  ,202    ,1);
 insert into table1 values(100  ,203    ,1);
 insert into table1 values(101  ,204    ,1);
 insert into table1 values(101  ,205    ,1);
 insert into table1 values(102  ,206    ,1);

Update query:

 with cte as(
    select col1,col2,col3, row_number()over (partition by col1 order by col2) rn from table1
 )
 update table1 set col3=cte.rn
 from cte
 where table1.col1=cte.col1 and table1.col2=cte.col2;

In above query row_number()over (partition by col1 order by col2) will generate an unique sequence for each distinct value in col1 starting from 1 and order by col2.

Select query:

 select * from table1;

Output:

col1	col2	col3
100	200	1
100	201	2
100	202	3
100	203	4
101	204	1
101	205	2
102	206	1

db<>fiddle here