UPDATE z ORDER BY w jednym zapytaniu na przykładzie orderingu danych

Wstęp

Mamy przykładową tabelę z userami:

DROP TABLE users;
CREATE TABLE users(
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	created TIMESTAMP DEFAULT NOW()
);

INSERT INTO users(name) VALUES('Marcin');
INSERT INTO users(name) VALUES('Bartek');
INSERT INTO users(name) VALUES('Agnieszka');
INSERT INTO users(name) VALUES('Dominika');

Na tabeli wykonywane były wielokrotnie operacje INSERT/UPDATE/DELETE przykładowo:

UPDATE users SET name = 'Ania' WHERE name = 'Bartek';
UPDATE users SET name = 'Dalia' WHERE name = 'Marcin';
DELETE FROM users WHERE name = 'Ania';

Zatem SELECT bez orderu wyświetli nam dane posortowane względem kolejności modyfikacji:

SELECT * FROM users;

psql=> select * from users;
 id |   name    |          created
----+-----------+----------------------------
  3 | Agnieszka | 2009-12-19 10:41:49.324851
  4 | Dominika  | 2009-12-19 10:41:49.958792
  1 | Dalia     | 2009-12-19 10:41:49.297223
(3 rows)

W interface aplikacji chcemy oprogramować własną obsługę kolejności rekordów. Dodajemy zatem kolumnę, która będzie obsługiwać tą funkcjonalność.

ALTER TABLE users ADD COLUMN order_id INTEGER;

Problem

Musimy teraz uzupełnić kolumne order_id o poprawne wartości. Kolejność orderu danych, które znajdują się w bazie powinna być kolejności wprowadzania tych danych do tabeli (czyli zgodna z kolejnością pola id).

Aby zrealizować poprawne uzupełnienie orderu tworzymy pomocniczą sekwencję, która wykorzystamy do wygenerowania kolejnych liczb do orderu:

DROP SEQUENCE order_id_seq;
CREATE SEQUENCE order_id_seq;

Wykonujemy oczywiste zapytanie przypisujące rekordom z tabeli users kolejne wartości sekwencji:

UPDATE users SET order_id = NEXTVAL('order_id_seq');  

Niestety przez to, że dane nie były ustawione w dobrej kolejności ORDER ustawiony jest niepoprawnie.

psql=> SELECT * FROM users;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        1
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        2
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        4
(3 rows)

Rozwiązanie

Aby UPDATE poprawnie ustawił kolejność orderu należy nieco zmodyfikować zapytanie i nadać danym z tabeli users odpowiednią kolejność.

W tym celu tworzymy podzapytanie sorted_ids, w którym poprzez ORDER BY ustalamy kolejność rekordów, które następnie w zapytaniu nadrzędnym zostaną zmodyfikowane właśnie w tej kolejności.

UPDATE users
	SET order_id = correct_order_id
FROM
	(
	SELECT 
		id, nextval('order_id_seq') as correct_order_id
	FROM
		users
  	ORDER BY 
		id ASC
	) as sorted_ids
WHERE
	users.id = sorted_ids.id; 

select * from users order by order_id;

Jak widać dane zostały pięknie zorderowane:

psql=> select * from users order by order_id;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        1
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        2
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        3
(3 rows)

Przy okazji wspomnę, że w aplikacji łatwiej obsłużyć ordering odwrotny, tzn. wiersze, które posiadają największą wartość order_id są na czele a nie na końcu listy. Przy takim podejściu przy wstawianiu nowego rekordu pole order_id powinno mieć wartość MAX(order_id) + 1. Dla orderingu odwrotnego należy zmienić w podzapytaniu kolejność sortowania z id ASC na id DESC.

Należy także dbać o to, aby przy usunięciu rekordu prenumerować order_id wszystkich rekordów, które znajdują się 'nad nim’ o -1.