Free tools Windows power users keep installed
One-click scans. No signup required.
Use SQL to profile a table, identify questionable records, apply explicit cleaning rules, and summarize the results. The safest approach is to inspect first, preview every proposed change, and separate three jobs: finding anomalies, deciding what they mean, and preventing invalid data from entering again. The examples below use PostgreSQL; check your database’s documentation before relying on syntax or edge-case behavior in another engine.
Start by defining what a row represents
Before changing data, establish the table’s grain: what one row is supposed to represent. A repeated customer ID, for example, may be an error in a table intended to have one row per customer, but entirely valid in an orders table. Also identify the database engine, table definition, key columns, and any existing rules.
As an Amazon Associate I earn from qualifying purchases.
Inspect representative rows and column types, then profile the table. This PostgreSQL query gives a row count and counts NULL values in selected columns:
SELECT
COUNT(*) AS row_count,
COUNT(*) FILTER (WHERE customer_id IS NULL) AS missing_customer_ids,
COUNT(*) FILTER (WHERE order_total IS NULL) AS missing_order_totals
FROM orders;COUNT(*) counts rows; COUNT(column) counts only rows where that expression is not NULL. PostgreSQL’s built-in aggregates generally ignore NULL inputs, so the two counts answer different questions. See the PostgreSQL documentation on aggregate functions.
#1 Best Overall
Profile values and suspected keys before deciding what to change:
SELECT status, COUNT(*) AS rows_per_status
FROM orders
GROUP BY status
ORDER BY rows_per_status DESC;
SELECT customer_id, COUNT(*) AS rows_per_id
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY rows_per_id DESC;These queries surface distributions and candidate duplicates; they do not establish that a value is wrong. Write down the business rules for required fields, valid ranges, and how to select a canonical record before editing.
Distinguish missing values from zero and other values
In PostgreSQL, NULL represents an unknown or absent value, not zero, an empty string, or a literal word such as “unknown.” Decide what a missing value means in the specific column before replacing or excluding it. For example, a missing order total is not necessarily equivalent to a zero-dollar order.
Rank #2
Most PostgreSQL aggregates ignore NULL inputs. In addition, SUM over no selected rows returns NULL, not zero. Use COALESCE only when a fallback is meaningful for the question:
SELECT COALESCE(SUM(order_total), 0) AS total_order_value
FROM orders
WHERE order_date >= DATE '2026-01-01';Here, the query deliberately reports zero if there are no qualifying non-NULL totals. That choice changes the presentation of “no aggregate result” into a numeric zero; it should match the intended interpretation. PostgreSQL documents these aggregate behaviors in its aggregate reference.
Find duplicate candidates and choose a record rule
SELECT DISTINCT removes identical rows from a query’s output. It does not decide which source record to preserve when rows share a business key but differ in other fields. In PostgreSQL, DISTINCT ON can return one row per key, but the chosen row is unpredictable unless the ordering determines which record comes first.
For instance, if the rule is “keep the most recently updated customer record, breaking ties by highest customer row ID,” express both parts of that rule in the ordering:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →SELECT DISTINCT ON (customer_id)
customer_id, email, updated_at
FROM customer_imports
ORDER BY customer_id, updated_at DESC, import_row_id DESC;This is a selection rule, not proof that the chosen record is correct. Confirm that the key and tie-breakers reflect the business definition, and inspect the candidates before deleting or overwriting anything. PostgreSQL explains duplicate elimination in SELECT lists and the ordering requirements for SELECT and DISTINCT ON.
Understand query order before interpreting a summary
A summary describes the rows that reach its grouping and aggregate stage, not necessarily every row in the table. In PostgreSQL’s documented SELECT processing, filtering determines the input rows; grouping and aggregate calculations operate on that input; result expressions and duplicate elimination shape the output; then ordering and limiting affect what is returned. Moving a filter or limit can therefore change the meaning of an analysis.
Rank #4
- Python Data Science Handbook
For example, this query counts orders by status only after restricting the date range:
SELECT status, COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY status
ORDER BY order_count DESC;Do not limit the input records before calculating a table-wide total unless a subset is what you intend to analyze. PostgreSQL’s SELECT documentation describes the processing behavior and syntax.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Turn confirmed rules into constraints
Queries can detect suspicious values and help clean existing rows. Constraints express rules the database should enforce on future writes. PostgreSQL supports NOT NULL, CHECK, UNIQUE, primary-key, and foreign-key constraints. Use them only after confirming the rule and resolving existing violations; a constraint cannot infer what the business considers valid.
Best Value
- Required value: use
NOT NULLwhen a value must be present. - Allowed range or condition: use
CHECKfor a condition such as a nonnegative amount. - Unique identity: use
UNIQUEor a primary key when the key must identify one row. - Relationship: use a foreign key when a value must refer to a valid row in another table.
A PostgreSQL CHECK constraint passes when its expression evaluates to NULL, so it does not by itself require a value. Pair it with NOT NULL when presence is part of the rule. Also, ordinary PostgreSQL uniqueness permits multiple rows with NULL in a constrained column, so a unique constraint is not a substitute for a required-value rule. Review the PostgreSQL constraints documentation for these behaviors and syntax.
Apply changes reversibly and validate the result
- Identify the engine, table grain, types, and keys. Confirm what each row represents and which columns define identity.
- Profile before editing. Record row counts, NULL counts, value distributions, and duplicate-key candidates.
- Write the rule. Specify valid values, required fields, and the selection rule for records that share a key.
- Preview candidate changes with SELECT. Inspect the exact rows that would be changed or excluded, and compare them with the rule.
- Plan recovery. Use an appropriate backup and transaction strategy for the size and risk of the change; do not run destructive statements before confirming how to recover.
- Make the change, then compare. Recheck row counts and key distributions, run validation queries, and add constraints where confirmed rules should apply to future writes.
For order-sensitive aggregates, specify input ordering when the aggregate’s result depends on sequence. PostgreSQL documents ordering within aggregate calls in its aggregate reference. Syntax and edge cases can differ across database engines and versions, so validate examples against the documentation for the system you use.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
