Write a safe duplicate-cleanup DELETE you can dry-run

About this exercise

Write a PostgreSQL DELETE that removes duplicate contacts for tenant_id 42 only. Emails that differ only in case count as the same, and for each email you keep the OLDEST row, the one with the lowest id, because invoices link to it. Wrap it as BEGIN; ... ROLLBACK; so the reviewer can dry-run it, with RETURNING id so they see what would go. Exactly 1 code block of at most 14 lines, under 25 words of text.

Common mistakes when you ask AI for this

• Touching every tenant. Without the tenant filter, the AI's DELETE also wipes tenant 7's rows. Try: Add: "Only tenant_id = 42."
• Case-sensitive matching. Ana@ and ana@ look different to a plain comparison, so a duplicate stays. Try: Add: "Emails that differ only in case are duplicates: use lower(email)."
• Keeping the wrong row. The AI usually keeps the newest row, but invoices point at the oldest one. Try: Add: "Keep the row with the lowest id per email."
• Running it for real. A DELETE with COMMIT can't be reviewed first. Try: Add: "Wrap in BEGIN; ... ROLLBACK; with RETURNING id."
• A query plus alternatives. The AI offers a CTE version, a USING version and a lecture. Try: Add: "One statement block in one code block, no alternatives."

What a strong prompt includes

• Limits to one tenant
• Handles case
• Gives the keep rule
• Asks for a dry run
• Asks to see what's deleted

Practice this now ↑

Related exercises

• Add a cron line that matches the others in a crontab (Coding AI · Product managers)
• Add dark mode by overriding your existing CSS variables (Coding AI · Software developers)
• Add input checks to your number guessing game (Coding AI · High school students)
• Add input validation to a Java method with exact messages (Coding AI · Software developers)
• Add labels to a signup form without rewriting the page (Coding AI · High school students)