Prompt InFlow
CodingDatabaseIntermediate30 minSaves 30 minutes

SQL Duplicate Row Cleanup with Transactional Safety

For data engineers, generate a robust SQL solution to identify and safely remove duplicate customer records, preserving the earliest entry within a transaction, ensuring data integrity.

Generate a comprehensive SQL solution for data engineers to identify and safely remove duplicate customer records. The output prioritizes transactional safety, preserving the earliest entry by `created_at`, and includes DDL, test data, cleanup query, execution plan notes, and

ClaudeChatGPTGemini

READY-TO-USE PROMPT

Copy Prompt

prompt.txt
Role: Database Engineer

Context: You are tasked with cleaning a production `customers` table that has accumulated duplicate records. The primary goal is to remove duplicates while preserving the oldest record based on the `created_at` timestamp. This operation must be transactional and consider performance implications for large datasets.

Task: Generate a comprehensive SQL solution. This solution must include:

1.  **DDL (Data Definition Language):** A `CREATE TABLE` statement for a sample `customers` table with `id` (PK), `email` (unique, but currently duplicated), `name`, and `created_at` columns.
2.  **Test Data:** `INSERT` statements for at least 5-7 rows, including several duplicate `email` entries with varying `created_at` timestamps to demonstrate the problem.
3.  **Duplicate Detection Query:** A `SELECT` query that clearly identifies all duplicate `email` entries and, for each duplicate set, highlights which `id` would be kept (the one with the earliest `created_at`).
4.  **Transactional Cleanup Query:** A `DELETE` statement wrapped in a `BEGIN TRANSACTION` / `COMMIT` block that removes all duplicate `customers` rows, keeping only the record with the earliest `created_at` for each `email`. Include a `ROLLBACK` option.
5.  **Expected Execution Plan Notes:** Briefly describe the likely execution plan for the `DELETE` query, especially concerning how it might handle large datasets and the importance of indexing.
6.  **Index Recommendation:** Suggest an appropriate index to optimize the duplicate detection and removal process.

Constraints:
*   The solution must be compatible with PostgreSQL syntax, but structured for readability and portability.
*   Assume `{{duplicate_column}}` (default: `email`) is the column used to identify duplicates.
*   The `created_at` column determines which record to keep (earliest).
*   The cleanup must be safe and atomic, hence the transaction.
*   The output should directly provide the SQL code and explanations, not conversational text.

Output:
A complete SQL script including DDL, test data, duplicate detection query, transactional cleanup query, execution plan notes, and index recommendations. Ensure the table name used is `{{customer_table_name}}` (default: `customers`).

Estimated results

DifficultyIntermediate
Setup time30 min
Time saved30 minutes
Best modelsClaude, ChatGPT, Gemini
Best audienceData Management, Software Development

Editor's note

Why this prompt matters

Production databases frequently face data integrity challenges, with duplicate records being a common culprit. For customer tables, these duplicates can lead to miscommunication, incorrect analytics, and operational inefficiencies. Identifying and safely removing these redundant entries, especially when the 'correct' record is determined by a timestamp like created_at, is a recurring task for data engineers and database administrators.

This workflow addresses the critical need for a structured and secure method to perform such cleanups. It's designed for situations where preserving the oldest valid record is paramount, and the deletion process must be atomic to prevent data corruption. Rather than crafting complex SQL from scratch for each instance, this approach provides a templated solution that accounts for DDL, test data, clear identification of duplicates, and a transactional cleanup strategy.

Engineers should reach for this workflow when preparing for data migrations, resolving post-import data discrepancies, or establishing routine data hygiene processes. It ensures that sensitive operations on live data are performed with a high degree of control and predictability, minimizing risk while maintaining data quality.

Anatomy

Prompt engineering breakdown

Role

The prompt clearly defines the persona as a "Database Engineer," setting the expectation for technical depth and specific SQL knowledge required for the task.

Context

It provides a realistic scenario of cleaning a production table with accumulated duplicate records, emphasizing key considerations like preserving the oldest record and transactional safety for a large dataset.

Goal

The explicit list of required outputs (DDL, test data, queries, plan notes, index recommendation) ensures a comprehensive and structured response from the model.

Constraints

Specific constraints like PostgreSQL compatibility, the use of placeholders for `duplicate_column` and `customer_table_name`, and the requirement for atomic operations guide the model towards a precise and usable solution.

Output format

The instruction to provide "A complete SQL script including DDL..." dictates the exact structure of the response, preventing conversational text and ensuring direct usability of the generated code.

Why this structure works

This structure works because it uses role priming to establish the model's expertise, explicit constraints to guide the technical details, and structured output definition to ensure the response is immediately actionable. This combination minimizes ambiguity and focuses the model on delivering a production-ready solution.

Pick your version

Prompt variations

BeginnerWorks with any model

When you need a straightforward SQL script to identify and remove duplicate rows, prioritizing clarity over advanced performance tuning or transactional nuances.

prompt.txt
As a Database Helper, your task is to clean up a `{{customer_table_name}}` table that has accumulated duplicate entries. For each group of duplicate `{{duplicate_column}}` values, we need to keep only the oldest record, based on its `created_at` timestamp. Please provide a simple `CREATE TABLE` statement for `{{customer_table_name}}`, along with some `INSERT` statements to create test data that clearly shows the duplicate problem. Then, generate a `SELECT` query to easily find these duplicates. Finally, provide a `DELETE` query to remove the extra rows, ensuring we only keep the earliest one for each `{{duplicate_column}}`. Focus on clear, easy-to-understand SQL code compatible with a PostgreSQL database.
ProfessionalBest with claude

For production environments where a robust, performance-aware, and transactionally safe SQL solution is paramount for data integrity tasks.

prompt.txt
Assume the role of a Senior Database Engineer. You are tasked with resolving data integrity issues in a live `{{customer_table_name}}` table by eliminating duplicate records. The core requirement is to retain the earliest entry for each unique `{{duplicate_column}}` value, determined by the `created_at` timestamp. Your deliverable must be a complete PostgreSQL-compatible SQL package, including DDL for the `{{customer_table_name}}` table, sample `INSERT` statements to simulate duplicates, a `SELECT` query to precisely identify duplicates for removal, and a transactional `DELETE` statement to safely execute the cleanup. Conclude with notes on the anticipated execution plan and an optimized index recommendation.
Short VersionWorks with any model

When you need a quick, concise prompt to get a functional SQL script for duplicate removal without extensive explanation or detailed breakdown.

prompt.txt
Provide a PostgreSQL SQL solution to clean duplicates from a `{{customer_table_name}}` table, keeping the oldest record by `created_at` for each `{{duplicate_column}}`. Include `CREATE TABLE`, `INSERT` test data with duplicates, a `SELECT` query to identify them, and a transactional `DELETE` statement. Also, suggest an index and briefly note the expected execution plan for the delete operation. Ensure the output is direct SQL and explanations.
EnterpriseBest with gemini

In regulated or high-stakes environments where data cleanup operations require formal procedures, risk assessment, and stakeholder communication beyond just technical execution.

prompt.txt
As a Lead Data Governance Engineer, you are responsible for a critical data quality initiative to rectify duplicate `{{duplicate_column}}` entries within the `{{customer_table_name}}` production table. The mandate is to ensure data integrity by preserving the original (earliest `created_at`) record while safely purging duplicates. Your solution must include a detailed PostgreSQL SQL script covering DDL, realistic test data, a comprehensive duplicate identification query, and a fully transactional `DELETE` operation with explicit `ROLLBACK` provisions. Additionally, include an impact analysis brief, a proposed rollback strategy, post-cleanup validation steps, and an index optimization plan, all suitable for stakeholder review and audit compliance. The solution should facilitate clear communication regarding risks and mitigation.

What you'll get

Expected output

-- DDL: Create Table CREATE TABLE customers ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL, name VARCHAR(255), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );

-- Test Data INSERT INTO customers (email, name, created_at) VALUES ('alice@example.com', 'Alice Smith', '2023-01-01 10:00:00Z'), ('bob@example.com', 'Bob Johnson', '2023-01-05 11:00:00Z'), ('alice@example.com', 'Alice Smith Duplicate', '2023-01-02 12:00:00Z'), -- Duplicate, created later ('charlie@example.com', 'Charlie Brown', '2023-01-10 09:00:00Z'), ('bob@example.com', 'Bob Johnson Duplicate', '2023-01-06 13:00:00Z'), -- Duplicate, created later ('alice@example.com', 'Alice Smith Another Dup', '2023-01-03 14:00:00Z'), -- Another duplicate, created later ('david@example.com', 'David Lee', '2023-01-15 15:00:00Z');

-- Duplicate Detection Query -- Identifies all duplicate emails and marks which 'id' would be kept (earliest created_at) WITH RankedCustomers AS ( SELECT id, email, name, created_at, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC, id ASC) as rn FROM customers ) SELECT rc.id, rc.email, rc.name, rc.created_at, CASE WHEN rc.rn = 1 THEN 'KEEP' ELSE 'REMOVE' END as action_plan FROM RankedCustomers rc WHERE rc.rn > 1 OR EXISTS (SELECT 1 FROM RankedCustomers rc2 WHERE rc2.email = rc.email AND rc2.rn = 1 AND rc2.id != rc.id) ORDER BY rc.email, rc.created_at;

-- Transactional Cleanup Query BEGIN;

DELETE FROM customers WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC, id ASC) as rn FROM customers ) AS RankedDuplicates WHERE RankedDuplicates.rn > 1 );

-- Optional: SELECT * FROM customers; -- To verify changes before COMMIT -- ROLLBACK; -- Use this to undo changes if something went wrong COMMIT;

-- Expected Execution Plan Notes -- For the DELETE statement, the database typically performs a full table scan or an index scan if a suitable index exists on 'email' and 'created_at'. -- The CTE (RankedDuplicates) will first partition the data by 'email' and order by 'created_at'. This operation can be resource-intensive on large datasets without an optimal index. -- PostgreSQL's query planner will likely use a WindowAgg node for ROW_NUMBER(), which might require sorting the entire table by 'email' and then 'created_at' if no index supports this ordering. -- The subsequent DELETE operation then targets specific 'id' values found by the CTE. If the number of IDs to delete is large, this could lead to significant I/O. -- An efficient index is crucial to speed up the partitioning and ordering phase of the window function.

-- Index Recommendation CREATE INDEX idx_customers_email_created_at ON customers (email, created_at ASC); -- This composite index will allow the database to quickly identify and order records by 'email' and then 'created_at', significantly optimizing the ROW_NUMBER() window function and thus the duplicate detection and deletion process.

Under the hood

Why this prompt works

This prompt workflow succeeds by employing several prompt engineering techniques that guide the model towards a comprehensive, production-ready solution. Firstly, role priming as a 'Database Engineer' immediately sets the expected expertise and output quality, ensuring the generated SQL is not just functional but also considers best practices for data integrity and performance.

The structured output requirement, specified through a numbered list of mandatory components like DDL, test data, duplicate detection query, transactional cleanup, execution plan notes, and index recommendations, is crucial. This forces the model to deliver a complete package, addressing the problem from setup to optimization, rather than just a single, isolated query. A one-liner would typically only provide the DELETE statement, omitting critical context for implementation and maintenance.

Furthermore, explicit constraints dictate the specific database (PostgreSQL), the transactional nature of the cleanup, and the logic for preserving the earliest record (created_at). These constraints prevent generic responses and ensure the solution is tailored and safe for a production environment. The inclusion of contextual details about the customers table schema (id, email, name, created_at) enables the model to generate accurate DDL and relevant test data, making the example immediately actionable and understandable.

Model fit

Best AI models for this prompt

Claude

Claude excels at generating well-structured, verbose SQL code with detailed explanations. Its strong contextual understanding helps in creating transaction-safe queries and logical plan notes, adhering closely to the specified constraints. However, it may sometimes produce overly generic index recommendations if not prompted for specific database types. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT is effective for producing functional SQL solutions, often with good explanations for DDL, DML, and transactional logic. It generally provides coherent execution plan notes and relevant index suggestions. Verify its SQL dialect specifics, as its output can sometimes lean towards a generic SQL standard rather than a specific RDBMS like PostgreSQL without explicit instruction. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini generates concise and accurate SQL, often prioritizing direct code over lengthy explanations, which can be efficient for experienced users. It handles transactional constructs and data manipulation effectively. Ensure to review the generated execution plan notes and index recommendations for depth, as they can sometimes be less detailed compared to other models. See the full Gemini hub for deeper guidance.

When to use

  • When your customers table has known duplicate email entries that need systematic removal.
  • As part of a data migration where the source system allowed non-unique identifiers.
  • For routine data quality maintenance tasks on critical customer datasets.
  • Before applying a UNIQUE constraint on the email column to prevent future duplicates.
  • When an audit reveals multiple customer records for the same email, and the earliest created_at indicates the primary record.

When not to use

  • If you need to merge data from duplicate rows rather than simply deleting the older ones.
  • When the created_at column is not a reliable indicator for which record should be preserved.
  • For tables where

Get more from it

Pro tips

  • 1

    Always execute the duplicate detection `SELECT` query first to verify exactly which records will be affected before committing any changes.

  • 2

    Test the full cleanup process on a staging environment with a production-like dataset to gauge performance and identify potential issues.

  • 3

    Perform a full backup of the target table or database immediately before running the `DELETE` statement in a production environment.

  • 4

    Consider adding a `UNIQUE` constraint on the `email` column after cleanup to prevent the reintroduction of duplicates.

  • 5

    Monitor transaction logs and system resources during large cleanup operations to prevent disk space exhaustion or performance bottlenecks.

  • 6

    Adjust the `ORDER BY` clause within `ROW_NUMBER()` if the 'earliest' record logic needs to change, e.g., to keep the latest record.

Don't ship this

Common mistakes

  • ✗ Running the `DELETE` statement without wrapping it in a transaction block, risking irreversible data loss.

    Fix — Always enclose the cleanup query within `BEGIN TRANSACTION` and `COMMIT` statements, with a `ROLLBACK` option.

  • ✗ Not validating the `SELECT` detection query results, leading to unintended deletion of correct records.

    Fix — Thoroughly review the output of the duplicate detection query to confirm it targets the correct rows for removal.

  • ✗ Neglecting to create the recommended index before executing the cleanup query on large tables.

    Fix — Implement the suggested index on `(email, created_at)` to significantly improve query performance and reduce lock times.

  • ✗ Assuming `created_at` is the definitive column for retention without verifying its data integrity.

    Fix — Confirm that `created_at` accurately reflects the desired primary record for each duplicate group before running the cleanup.

  • ✗ Running large-scale `DELETE` operations during peak database usage hours, causing performance degradation.

    Fix — Schedule significant data cleanup tasks during off-peak hours or dedicated maintenance windows to minimize user impact.

People also ask

Frequently asked questions

Q.Can this method be adapted to identify duplicates based on multiple columns, not just `email`?

Yes, modify the PARTITION BY clause in the ROW_NUMBER() function to include all columns that define a duplicate set. For example, PARTITION BY email, first_name.

Q.What happens if `created_at` has `NULL` values for some records within a duplicate set?

The ORDER BY created_at ASC clause will typically treat NULLs as either the lowest or highest value depending on the database. PostgreSQL's NULLS LAST ensures non-null created_at records are preferred, placing NULLs last in the ordering.

Q.How does this approach perform on very large tables, for example, millions of rows?

Performance on large tables heavily relies on the recommended index on (email, created_at). Without it, the ROW_NUMBER() calculation can cause full table scans, leading to slow execution and potential database locking issues. Test thoroughly.

Q.Is it possible to keep the *latest* created record instead of the earliest?

Yes, to retain the most recent record, change ORDER BY created_at ASC to ORDER BY created_at DESC within the ROW_NUMBER() function in the duplicate detection and cleanup queries.

Q.What if I need to audit the deleted rows or move them to an archive table instead of permanent deletion?

Instead of a direct DELETE, you would first INSERT the identified duplicate rows (where rn > 1) into an archive table. Then, perform the DELETE operation from the original table.

Version 1.0Last reviewed July 20, 2026
Reviewed by PromptInFlow Editorial Team