By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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);
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
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;
UPDATE 7
select * from table1;
| col1 | col2 | col3 |
|---|---|---|
| 100 | 200 | 1 |
| 100 | 201 | 2 |
| 100 | 202 | 3 |
| 100 | 203 | 4 |
| 101 | 204 | 1 |
| 101 | 205 | 2 |
| 102 | 206 | 1 |
SELECT 7