duplicates.sql

-- That query gives you a list of duplicates without the first record 
-- you can delete them later
with d as (
    select entity_id, locale_id, t.string, count(t.id) as duplicates,
        array_agg(t.id order by approved desc, rejected asc, date desc) as ids
    from base_translation t
    join base_entity e
    ON (t.entity_id=e.id)
    join base_resource r
    ON (e.resource_id=r.id)
    WHERE r.path like '%.ftl'
    group by locale_id, entity_id, t.string
    order by count(t.id) desc
)
SELECT unnest(ids[2:100]) from d;

-- The same query but with DELETE statement, may require additional cleanups in translationmemoryentry table though
with d as (
    select entity_id, locale_id, t.string, count(t.id) as duplicates,
        array_agg(t.id order by approved desc, rejected asc, date desc) as ids
    from base_translation t
    join base_entity e
    ON (t.entity_id=e.id)
    join base_resource r
    ON (e.resource_id=r.id)
    WHERE r.path like '%.ftl'
    group by locale_id, entity_id, t.string
    order by count(t.id) desc
)
DELETE FROM base_translation WHERE id IN (SELECT unnest(ids[2:100]) from d);