Skip to content

Instantly share code, notes, and snippets.

@Arqentum

Arqentum/postgresql_inplace_dedupe.sql

Created June 19, 2024 01:15
Show Gist options
  • Save Arqentum/8f0c2c4d0cd49b1e0f664bd889f8ff0b to your computer and use it in GitHub Desktop.
Save Arqentum/8f0c2c4d0cd49b1e0f664bd889f8ff0b to your computer and use it in GitHub Desktop.
Download ZIP
#Postgres inplace dedupe
Raw
postgresql_inplace_dedupe.sql
# /*SD: inplace distinct. MYR-caused duplicate from union query above, not worth the full debug*/
cursor.execute(""" DELETE FROM incentive_activation_rr_invoices a USING (
SELECT MIN(ctid) as ctid, invoice_id, invoice_li_id, product /*SD: PK*/
FROM incentive_activation_rr_invoices
GROUP BY (invoice_id, invoice_li_id, product) HAVING COUNT(*) > 1
) b
WHERE true
and a.invoice_id = b.invoice_id
and a.invoice_li_id = b.invoice_li_id
and a.product = b.product
AND a.ctid <> b.ctid;""")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment