Postgres 14

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 table_name(location VARCHAR(50), rank_ INT);

Insert Into table_name Values('location_A',1),
('location_B',2),
('location_C',3),
('location_D',4),
('location_E',5),
('location_F',6);

CREATE TABLE



INSERT 0 6


SELECT location,
       RANK() OVER (ORDER BY CASE 
                                   WHEN location = 'location_E' THEN 1
                                   WHEN location = 'location_D' THEN 2
                                   ELSE 3
                                   END, rank_) rank_
FROM table_name
  


location
rank_


location_E
1

location_D
2

location_A
3

location_B
4

location_C
5

location_F
6



SELECT 6


WITH ranks AS
(
  SELECT location,
       RANK() OVER (ORDER BY CASE 
                                   WHEN location = 'location_E' THEN 1
                                   WHEN location = 'location_D' THEN 2
                                   ELSE 3
                                   END, rank_) rank_
  FROM table_name
)

UPDATE table_name T
SET rank_ = R.rank_
FROM ranks R
WHERE T.location = R.location;

UPDATE 6


SELECT * FROM table_name;


location
rank_


location_E
1

location_D
2

location_A
3

location_B
4

location_C
5

location_F
6



SELECT 6

location	rank_
location_E	1
location_D	2
location_A	3
location_B	4
location_C	5
location_F	6