23 lines
849 B
SQL
23 lines
849 B
SQL
-- Here's an SQL script that will find and remove snapshots without port numbers
|
|
-- when there exists a duplicate with the default port 1965.
|
|
|
|
-- Before running this DELETE though, you might want to
|
|
-- verify the matches first with this SELECT:
|
|
WITH duplicates AS (
|
|
SELECT s1.id as id_without_port, s2.id as id_with_port, s1.url as url_without_port, s2.url as url_with_port
|
|
FROM snapshots s1
|
|
JOIN snapshots s2
|
|
ON s2.url = s1.url || ':1965'
|
|
)
|
|
SELECT * FROM duplicates;
|
|
|
|
-- Now delete them for real:
|
|
WITH duplicates AS (
|
|
SELECT s1.id as id_without_port, s2.id as id_with_port, s1.url as url_without_port, s2.url as url_with_port
|
|
FROM snapshots s1
|
|
JOIN snapshots s2
|
|
ON s2.url = s1.url || ':1965'
|
|
)
|
|
DELETE FROM snapshots
|
|
WHERE id IN (SELECT id_without_port FROM duplicates);
|