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;
Telegram: DB developers channel