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.
Przez ostatnie miesiące zaangażowany byłem w autorski projekt