Data Cleaning with SQL (PostgreSQL)

Data Cleaning with SQL

From Raw Tables to Business Insights · PostgreSQL

Choosing the right cleaning approach in PostgreSQL: Query-Based SQL, Set-Based ETL & Staging, and Procedural PL/pgSQL. Tap any step to see the syntax and what it does.

1SOURCE
RAW DATA
Loaded into PostgreSQL from multiple sources
Bulk-loaded with COPY or \copy, the fastest way to get flat files into Postgres.
Excel or Google Sheets saved as CSV, then imported into a staging table.
Pulled in live via foreign data wrappers (postgres_fdw) or an ETL tool.
JSON responses landed in jsonb columns, then flattened for cleaning.
2ASSESS

Data Quality Assessment

Profile every issue before choosing a cleaning path

NULLs and empty strings that break joins, filters and aggregates.
Exact or near-duplicate rows that inflate counts and metrics.
Numbers or dates stored as text, which block math, sorting and indexes.
Mixed case, stray whitespace and encoding that stop values from matching.
Rows that violate constraints or reference a parent that no longer exists.
Extreme or impossible values that distort every downstream metric.
Choose a path
3CLEAN
12 steps

Query-Based
SQL Cleaning

  • Syntax TRIM(col) · BTRIM(col, ' .') Example TRIM(' John Doe ') → 'John Doe' Removes leading and trailing spaces (or any characters you list) from text.
  • Syntax INITCAP(LOWER(col)) Example INITCAP('jOHN dOE') → 'John Doe' Standardises text case, lower, upper, or Title Case for names and labels.
  • Syntax REPLACE(col, 'old', 'new') Example REPLACE('555-1234', '-', '') → '5551234' Swaps every occurrence of a substring, ideal for fixing known bad values.
  • Syntax REGEXP_REPLACE(col, '\s+', ' ', 'g') Example REGEXP_REPLACE('a b c', '\s+', ' ', 'g') → 'a b c' Cleans patterns with regex, here collapsing repeated whitespace into one space.
  • Syntax COALESCE(col, 'N/A') Example COALESCE(NULL, 'N/A') → 'N/A' Replaces NULLs with a fallback value so gaps don't break reports.
  • Syntax NULLIF(col, '') Example NULLIF('', '') → NULL Turns empty strings into real NULLs so they are treated as missing.
  • Syntax col::numeric · CAST(col AS date) Example '42'::int + 8 → 50 Converts a value to the correct data type for maths, sorting and joins.
  • Syntax TO_DATE(col, 'DD/MM/YYYY') Example TO_DATE('15/01/2024','DD/MM/YYYY') → 2024-01-15 Parses text into a real date or number using an explicit format mask.
  • Syntax SPLIT_PART(col, ',', 1) Example SPLIT_PART('Doe,John', ',', 1) → 'Doe' Extracts one piece of a delimited string by its position.
  • Syntax SUBSTRING(col FROM '\d+') Example SUBSTRING('Order #1234' FROM '\d+') → '1234' Pulls out part of a string by position or regular expression.
  • Syntax SELECT DISTINCT col FROM t; Example SELECT DISTINCT country FROM users; → unique list Returns unique values, a quick way to profile and spot dirty data.
  • Syntax CASE WHEN col='Y' THEN true ELSE false END Example CASE WHEN status='Y' THEN 'Active' ELSE 'Inactive' END Recodes or standardises values conditionally, row by row.

BEST FOR

  • Small, ad-hoc fixes
  • One-off exploration
  • Learning SQL fundamentals
AutomationLow
Skill LevelBeginner
14 steps

Set-Based ETL
& Staging

  • Syntax CREATE TABLE stg AS SELECT * FROM raw; Example CREATE TABLE stg_orders AS SELECT * FROM orders_raw; Load raw data into a scratch table so you clean without touching the source.
  • Syntax INSERT INTO clean SELECT ... FROM stg; Example INSERT INTO customers(name, email) SELECT INITCAP(TRIM(name)), LOWER(email) FROM stg; Move cleaned rows into the final table in one set-based operation.
  • Syntax UPDATE t SET col = TRIM(col) WHERE col <> TRIM(col); Example UPDATE users SET email = LOWER(email) WHERE email <> LOWER(email); Fix values across thousands of rows at once, filtered by a condition.
  • Syntax DELETE FROM t a USING t b WHERE a.ctid < b.ctid AND a.key = b.key; Example DELETE FROM users a USING users b WHERE a.ctid < b.ctid AND a.email = b.email; Remove duplicate rows using ctid to keep exactly one of each.
  • Syntax WITH d AS (SELECT ...) SELECT * FROM d; Example WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users ) SELECT * FROM ranked WHERE rn = 1; Break complex cleaning into readable, chained steps.
  • Syntax ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) Example SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn FROM users; -- keep rn = 1 Rank rows per key so you can keep the latest and drop the rest.
  • Syntax LAG(col) OVER (ORDER BY id) Example amount - LAG(amount) OVER (ORDER BY day) → day-over-day change Compare across rows to spot gaps, jumps and anomalies.
  • Syntax JOIN ref r ON t.code = r.code Example SELECT t.*, r.country_name FROM sales t JOIN countries r ON t.iso = r.iso; Standardise codes and labels against a trusted reference table.
  • Syntax GROUP BY key HAVING COUNT(*) > 1 Example SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; → duplicates Aggregate for profiling and surface duplicates in one query.
  • Syntax ALTER TABLE t ADD CONSTRAINT uq UNIQUE (key); Example ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email); Enforce NOT NULL and UNIQUE rules so bad data can't get back in.
  • Syntax ALTER TABLE t ADD CHECK (age BETWEEN 0 AND 120); Example ALTER TABLE people ADD CHECK (age BETWEEN 0 AND 120); Reject out-of-range values at the database level, automatically.
  • Syntax INSERT ... ON CONFLICT (key) DO UPDATE SET ...; Example INSERT INTO users(email, name) VALUES ('a@x.com','A') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; Insert or update in a single statement, avoiding duplicate keys.
  • Syntax CREATE VIEW clean_v AS SELECT ...; Example CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; Expose a clean, reusable version of the data without copying it.
  • Syntax CREATE MATERIALIZED VIEW mv AS SELECT ...; Example CREATE MATERIALIZED VIEW daily_sales AS SELECT day, SUM(amount) FROM orders GROUP BY day; -- REFRESH MATERIALIZED VIEW daily_sales; Cache heavy cleaning results and refresh them on demand.

BEST FOR

  • Medium and large tables
  • Repeatable ETL pipelines
  • Business reporting
AutomationHigh
Skill LevelIntermediate
12 steps

Procedural
PL/pgSQL

  • Syntax CREATE FUNCTION clean(t text) RETURNS text AS $$ ... $$ LANGUAGE plpgsql; Example CREATE FUNCTION norm(t text) RETURNS text AS $$ BEGIN RETURN INITCAP(TRIM(t)); END; $$ LANGUAGE plpgsql; Wrap reusable cleaning logic in a callable function.
  • Syntax DO $$ BEGIN ... END $$; Example DO $$ BEGIN UPDATE users SET email = LOWER(email); END $$; Run a one-off procedural script without creating a permanent function.
  • Syntax CREATE TRIGGER trg BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION f(); Example CREATE TRIGGER clean_email BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION tidy_email(); Auto-clean every row the moment it is inserted or updated.
  • Syntax NEW.email := LOWER(TRIM(NEW.email)); RETURN NEW; Example CREATE FUNCTION tidy_email() RETURNS trigger AS $$ BEGIN NEW.email := LOWER(TRIM(NEW.email)); RETURN NEW; END; $$ LANGUAGE plpgsql; Standardise column values inside a BEFORE trigger.
  • Syntax FOR r IN SELECT ... LOOP ... END LOOP; Example FOR r IN SELECT id, email FROM users LOOP UPDATE users SET email = LOWER(r.email) WHERE id = r.id; END LOOP; Iterate over rows for row-by-row processing when a set query won't do.
  • Syntax BEGIN ... EXCEPTION WHEN others THEN ... END; Example BEGIN INSERT INTO clean SELECT * FROM stg; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'dupe skipped'; END; Catch and log errors so one bad row doesn't stop the whole job.
  • Syntax EXECUTE format('UPDATE %I SET ...', tbl); Example EXECUTE format('UPDATE %I SET name = TRIM(name)', tbl); Build and run SQL for any table or column at runtime, safely quoted.
  • Syntax CREATE DOMAIN email AS text CHECK (VALUE ~ '^.+@.+$'); Example CREATE DOMAIN email AS text CHECK (VALUE ~ '^.+@.+$'); CREATE TABLE t (addr email); Define a reusable, validated type once and apply it everywhere.
  • Syntax CREATE TYPE status AS ENUM ('active','inactive'); Example CREATE TYPE status AS ENUM ('active','inactive'); ALTER TABLE users ADD state status; Constrain a column to a fixed set of valid values.
  • Syntax SELECT cron.schedule('nightly','0 2 * * *','CALL clean_all();'); Example SELECT cron.schedule('dedupe','0 3 * * *', 'DELETE FROM stg a USING stg b WHERE a.ctid < b.ctid AND a.key = b.key'); Automate cleaning to run on a schedule, no manual step.
  • Syntax CREATE EXTENSION IF NOT EXISTS pg_trgm; Example SELECT * FROM users WHERE name % 'Jon'; -- fuzzy-matches 'John', 'Jon'… Add fuzzy matching (pg_trgm), unaccent and more for advanced cleaning.
  • Syntax -- V1__clean.sql (versioned & repeatable) Example -- V2__dedupe_users.sql (run in order by Flyway / Liquibase) Version-control reusable cleaning scripts across every environment.

BEST FOR

  • Enterprise pipelines
  • Advanced automation
  • Custom, governed rules
AutomationVery High
Skill LevelAdvanced
4VALIDATE
Cleaned & Validated Data
Consistent, deduplicated, correctly typed and analysis-ready
5ANALYZE

Analysis & Visualization

Query, view and serve clean data to any reporting tool

GROUP BY, SUM, AVG and window queries summarise clean data on the fly.
Saved queries that serve analysis-ready datasets to any tool.
Pre-computed, refreshable result sets for fast, heavy reporting.
Connect Metabase, Power BI, Tableau or Superset straight to Postgres.
6DECIDE
Business Intelligence & Decision Making
The payoff: clean data drives confident action
Formatted, refreshable outputs that keep stakeholders aligned on the same numbers.
Track key metrics against targets at a glance, so problems surface early.
Confident, data-backed choices by leadership instead of gut feel.
Spot the opportunities and trends that drive measurable, repeatable growth.
Align long-term direction with evidence rather than assumption.
★LEARNING JOURNEY
ROADMAPyour path from raw tables to automated, governed data
1Raw Data
2Load to Postgres
3Query Cleaning
4Set-Based ETL
5PL/pgSQL Automation
6Views & Analytics
7Dashboards
8Business Decisions
Data Cleaning with SQL · PostgreSQL · Query-Based SQL · Set-Based ETL · Procedural PL/pgSQL
Data Cleaning with SQL · PostgreSQL
Created by Ada Innocent Ada, NCMD
for Data Tribe · by Awake to Power Communications
© Awake to Power Communications 2026
Back To Top