Quick summary
Summarize this blog with AI
Clean a proposed result before changing stored data
Data cleaning should make invalid and uncertain records visible while retaining the original evidence. A trim, a cast, a duplicate rule, and an imputation are different decisions. This PostgreSQL 16 example produces a read-only staging report: original values, normalized candidates, typed values, and rejection reasons. It does not rewrite the source table or invent missing measurements.
Write a contract narrow enough to test
Our fictional feed requires a nonempty trimmed item ID, one record per ID, an allowed status, a non-negative amount with at most five integer digits and two fractional digits, and an ISO calendar date. Status comparison is lowercase. IDs remain text so leading zeros survive. Duplicate IDs are all quarantined because this feed supplies no trustworthy version or winner rule.
These rules are deliberate teaching choices. Real systems may permit negative refunds, multiple events for the same customer, or a different amount range. An extreme value is a review signal, not permission to replace it with a convenient average. Likewise, lowercasing identifiers or personal names can change their meaning; normalize only fields whose contract allows it.
Run the complete audit fixture
WITH raw(row_id,id_text,status_text,amount_text,date_text) AS (
VALUES (1,' 001 ',' Paid ','12.50','2026-01-01'),
(2,'002','paid','abc','2026-01-02'),
(3,'002','paid','9','2026-01-03'),
(4,'003','paid',NULL,'2026-02-30'),
(5,' ','cancelled','-1','2026-01-05'),
(6,'004','pending','0','2026-01-06')
), normalized AS (
SELECT *,NULLIF(BTRIM(id_text),'') AS item_id,
LOWER(BTRIM(status_text)) AS clean_status,
CASE WHEN amount_text ~ '^[0-9]{1,5}(\.[0-9]{1,2})?$'
THEN amount_text::numeric END AS amount,
CASE WHEN date_text ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
AND pg_input_is_valid(date_text,'date')
THEN date_text::date END AS event_date
FROM raw
), classified AS (
SELECT *,ARRAY_REMOVE(ARRAY[
CASE WHEN item_id IS NULL THEN 'missing_id' END,
CASE WHEN item_id IS NOT NULL AND COUNT(*) OVER(PARTITION BY item_id)>1
THEN 'duplicate_id' END,
CASE WHEN clean_status IS NULL OR clean_status NOT IN ('paid','pending','cancelled')
THEN 'invalid_status' END,
CASE WHEN amount IS NULL THEN 'invalid_amount' END,
CASE WHEN event_date IS NULL THEN 'invalid_date' END
],NULL) AS rejection_reasons FROM normalized
)
SELECT row_id,id_text,item_id,clean_status,amount,event_date,rejection_reasons
FROM classified ORDER BY row_id;row_id | id_text | item_id | clean_status | amount | event_date | rejection_reasons
1 | " 001 " | 001 | paid | 12.50 | 2026-01-01 | {}
2 | 002 | 002 | paid | NULL | 2026-01-02 | {duplicate_id,invalid_amount}
3 | 002 | 002 | paid | 9 | 2026-01-03 | {duplicate_id}
4 | 003 | 003 | paid | NULL | NULL | {invalid_amount,invalid_date}
5 | " " | NULL | cancelled | NULL | 2026-01-05 | {missing_id,invalid_amount}
6 | 004 | 004 | pending | 0 | 2026-01-06 | {}Quotes around the displayed whitespace IDs are explanatory; the retained id_text values contain the original spaces. The candidate item_id is separate. Every source row survives the audit report. The invalid February 30 date is rejected instead of being reformatted into a misleading valid-looking string.
Validate before casting
The numeric pattern bounds both syntax and length before casting. It intentionally rejects signs, exponent notation, commas, and empty values. The date uses an ISO-shape check plus PostgreSQL 16’s pg_input_is_valid to check the actual calendar value. The cast is inside the guarded CASE branch. Formatting a typed date does not validate the original raw text, and a pattern alone does not prove that February 30 exists.
pg_input_is_valid is version-specific and depends on the input function supporting error reporting; use the documented behavior for the target type. For other database versions or engines, choose their tested safe-conversion mechanism or validate in ingestion code. Do not paste SQL Server CONVERT or MySQL date parsing functions into an unlabeled PostgreSQL query. See input-validation functions and CASE.
Retain rejected records and distinguish missing from zero
Rows 1 and 6 are accepted; rows 2–5 are quarantined. The accepted amounts sum to 12.50. Row 6’s explicit zero is valid. Row 4’s absent amount is unknown, so it is not changed to zero or an average. Row 3 would otherwise look valid, but its duplicated ID conflicts with row 2; selecting either without provenance would invent an authority rule.
Multiple reasons can apply to one row. Consequently, counts by rejection reason may sum to more than the total rejected rows. The reconciliation is six source rows = two accepted + four rejected. Preserve row IDs and the source snapshot/cutoff so a corrected input can be traced to the original record. If a transformation changes a business identifier, also assess joins that depend on that identifier.
Present a decision-ready cleaning report
Report total input rows, accepted rows, rejected rows, changed-field counts, and null counts. Show examples of each rejection with private values appropriately protected. Keep raw, proposed, and approved values distinct. A downstream analysis should either use the accepted population under a stated policy or explicitly include unresolved records; silently dropping them can bias the result.
For a real stored correction, review an exact candidate set, obtain the required business approval, and apply a scoped transaction with concurrency guards and post-change reconciliation. A SELECT preview does not authorize a bulk UPDATE. A backup is useful only with a viable restore path. This article deliberately stays at the detection and proposal stage, where all six originals remain inspectable.
Test the policy, not just the happy path
Add a duplicate with conflicting values, a whitespace-only ID, a real zero, an unknown amount, an impossible date, and a status outside the allowed list. Then verify that accepted plus rejected equals input and that no invalid row disappears. Keep the reasons stable enough for monitoring, while changing the policy only through an explicit reviewed decision.
FAQ
Does DISTINCT clean duplicate business records? It only removes identical selected rows; it cannot decide which conflicting record is correct. Should missing amounts be imputed? Only under a justified analytical policy, with an imputation flag and retained originals. Is this relational normalization? No. Text/value standardization and database normalization address different problems.
