Fix: Make tags unique (empties product tags)

1 files changed, 19 insertions, 0 deletions

diff --git a/migrations/20241124111952_uniquetags.sql b/migrations/20241124111952_uniquetags.sql
new file mode 100644
index 0000000..ff8b6ef
--- /dev/null
+++ b/migrations/20241124111952_uniquetags.sql
@@ -0,0 +1,19 @@
+DELETE FROM producttag;
+CREATE TABLE IF NOT EXISTS newtag
+(
+    id   INTEGER PRIMARY KEY NOT NULL,
+    name TEXT                NOT NULL UNIQUE
+);
+
+WITH uniquetags AS (
+    SELECT id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
+    FROM tag
+)
+
+INSERT INTO newtag (id, name)
+SELECT id, name
+FROM uniquetags
+WHERE row_num = 1;
+
+DROP TABLE tag;
+ALTER TABLE newtag RENAME TO tag;