This describes a naive implementation how to fix missing gaps after a reordering. Table tbl contains two columns. The position column is of type decimal to make the reordering easier.
CREATE TABLE tbl(key varchar(255), position decimal(5,4));
We fill the table with the following statements
INSERT INTO tbl values('A', 1.0);
INSERT INTO tbl values('B', 2.0);
INSERT INTO tbl values('C', 3.0);
INSERT INTO tbl values('D', 4.0);
INSERT INTO tbl values('E', 5.0);
INSERT INTO tbl values('F', 6.0);
INSERT INTO tbl values('G', 7.0);
INSERT INTO tbl values('H', 8.0);
INSERT INTO tbl values('I', 9.0);
INSERT INTO tbl values('J', 10.0);
INSERT INTO tbl values('K', 11.0);
Our reordering operation reorders multiple rows to a new position based on their relative old position:
A A
*B C
C *B
-> *G
D *J
E D
F E
*G F
H H
I I
*J K
K
With the following UPDATE statement we execute the reordering operation. Please note, that we are using the fraction to move the row to the expected position:
-- option 1
UPDATE tbl AS t
SET position = n.position
FROM
(VALUES
('B', 3.1),
('G', 3.2),
('J', 3.3)
) AS n(key, position)
WHERE t.key = n.key;
-- option 2
UPDATE tbl SET position = 3.1 WHERE key = 'B';
UPDATE tbl SET position = 3.2 WHERE key = 'G';
UPDATE tbl SET position = 3.3 WHERE key = 'J';
When retrieving the updated rows, there are gaps for the old position of B, G and J:
SELECT * FROM tbl ORDER BY position;
Decimal fraction and gaps can be removed by reordering the whole set:
UPDATE tbl tbl_old SET
position = tbl_reordered.new_position
FROM (
SELECT key, row_number() OVER (ORDER BY position ASC) AS new_position FROM tbl
-- if you only want to reorder a small set of this table, the WHERE conditions would go here
) tbl_reordered
WHERE tbl_old.key = tbl_reordered.key;