SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE genre ( id bigint PRIMARY KEY, name varchar(100) NOT NULL, parent_genre_id bigint, FOREIGN KEY (parent_genre_id) REFERENCES genre(id) ); CREATE TABLE track ( id bigint PRIMARY KEY , name varchar(100) NOT NULL ); CREATE TABLE track_genre ( track_id bigint, genre_id bigint, PRIMARY KEY(track_id, genre_id), FOREIGN KEY (track_id) REFERENCES track(id), FOREIGN KEY (genre_id) REFERENCES genre(id) ); INSERT INTO genre(id, name, parent_genre_id) VALUES (1, 'pop', null), (2, 'rock', null), (3, 'blues', null), (4, 'russian pop', 1), (5, 'k-pop', 1), (6, 'euro pop', 1), (7, 'hard rock', 2), (8, 'metal', 2), (9, 'punk rock', 2), (10, 'delta blues', 3), (11, 'electric blues', 3), (12, 'heavy metal', 8), (13, 'trash metal', 8), (14, 'post punk', 9), (15, 'horror punk', 9); INSERT INTO track(id, name) VALUES (1, 'Hallowed Be Thy Name'), (2, 'Boys Don''t Cry'), (3, 'Riding With The King'), (4, 'You Give Love A Bad Name'), (5, 'Since I''ve Been Loving You'); INSERT INTO track_genre(track_id, genre_id) VALUES (1, 12), (2, 14), (3, 3), (4, 2), (4, 7), (5, 3), (5, 7); CREATE TABLE t ( track_id INT, genre_id INT, branch INT[] ); WITH RECURSIVE genre_tree AS ( SELECT id, name, parent_genre_id, ARRAY[id] AS branch -- Заменяем id на массив [id] FROM genre WHERE parent_genre_id IS NULL UNION ALL SELECT child.id, child.name, child.parent_genre_id, g.branch || child.id -- Конкатенируем текущий id с массивом branch FROM genre AS child JOIN genre_tree g ON g.id = child.parent_genre_id ) INSERT INTO t (track_id, genre_id, branch) SELECT track_id, genre_id, genre_tree.branch FROM track_genre JOIN genre_tree ON track_genre.genre_id = genre_tree.id; SELECT * FROM t; --CREATE TABLE t_temp AS --SELECT track_id, genre_id, unnest(branch) AS branch_new --FROM t; --DROP TABLE t; --ALTER TABLE t_temp RENAME TO t; --ALTER TABLE t ADD COLUMN branch_new INT; --UPDATE t SET branch_new = unnest(branch); --ALTER TABLE t DROP COLUMN branch; ALTER TABLE t ADD COLUMN branch_new INT; UPDATE t SET branch_new = ANY(SELECT unnest(branch) FROM t WHERE t.track_id = t.track_id); DELETE FROM T WHERE array_length(branches, 1) > 1; ALTER TABLE t DROP COLUMN branch; SELECT * FROM t; ALTER TABLE t DROP COLUMN genre_id; SELECT * FROM t; ALTER TABLE t RENAME COLUMN branch_new TO genre_id; WITH del AS (DELETE FROM t RETURNING *) INSERT INTO t SELECT DISTINCT * FROM del; SELECT * FROM t; --CREATE TEMPORARY TABLE temp_table AS --SELECT DISTINCT ON (track_id, genre_id) -- * --FROM t; --DROP TABLE t; --ALTER TABLE temp_table RENAME TO t; ALTER TABLE t ADD COLUMN track_name varchar(100); UPDATE t SET track_name = track.name FROM track WHERE t.track_id = track.id; ALTER TABLE t ADD COLUMN genre_name varchar(100); UPDATE t SET genre_name = genre.name FROM genre WHERE t.genre_id = genre.id; SELECT * FROM t ORDER BY track_id ASC;