Resolving Data Integrity Issues: Managing Duplicate Records in PostgreSQL

Introduction

In the North-South project, we recently encountered a data integrity challenge involving payment processing. Specifically, we identified an issue where policy payments were being recorded as duplicates. This type of data anomaly can ripple through financial reporting and user balance calculations if left unaddressed. We tackled this by implementing targeted SQL scripts to normalize the data and prevent future occurrences.

The Nature of Duplicate Data

Duplicate entries often creep into relational databases due to race conditions, retry logic, or lack of unique constraints at the application layer. When a process that should be idempotent (meaning it can be safely executed multiple times without changing the result beyond the initial application) is interrupted or retried incorrectly, you end up with multiple rows representing the same financial event.

Think of this like a library system that accidentally creates two library cards for the same book checkout. When the book is returned, the system might try to update the checkout record twice, leading to inconsistencies in the database state.

Identifying and Cleaning the Data

To resolve this, we focused on identifying the duplicate records based on policy identifiers and timestamps. Using common table expressions (CTEs) or window functions like ROW_NUMBER(), we can isolate the redundant entries.

-- Identify potential duplicate payment records
SELECT policy_id, payment_date, count(*)
FROM policy_payments
GROUP BY policy_id, payment_date
HAVING count(*) > 1;

-- Remove duplicates while keeping the oldest entry
DELETE FROM policy_payments
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY policy_id, payment_date ORDER BY created_at) as row_num
        FROM policy_payments
    ) t
    WHERE t.row_num > 1
);

Ensuring Future Stability

Beyond just deleting the noise, the long-term solution requires reinforcing the database schema with unique constraints. This ensures that the database itself rejects any attempt to insert a duplicate payment record before it even touches the disk.

-- Add a unique constraint to prevent duplicate entries
ALTER TABLE policy_payments 
ADD CONSTRAINT unique_policy_payment UNIQUE (policy_id, payment_date);

Conclusion

Data integrity is the bedrock of any financial-related application. By using window functions to identify duplicates and unique constraints to enforce business rules, we can ensure the reliability of the North-South platform.

Actionable Takeaway: Regularly audit your tables for high-frequency transaction data. If you find duplicates, don't just clear them—identify the root cause in your application logic and apply a unique constraint to your database schema to enforce data quality at the source.

Generated with Gitvlg.com