Cleaning Data in PostgreSQL Databases
Clean messy PostgreSQL tables with SQL: standardize strings, fill missing values, resolve duplicates, convert dates and split address columns.
Real-world data is messy. Text fields carry stray spaces, color names are spelled inconsistently, values are left empty, rows repeat, and dates sit in text columns. The work here is fixing those problems with SQL inside the database, as early in the pipeline as possible, using messy public records from New York City’s Open Data program, many of them parking violations.
What You Will Achieve
- Clean and standardize messy strings, including stray spaces and inconsistent color names, with pattern matching and string similarity functions.
- Measure how complete a table is, fill gaps with COALESCE, and flag duplicate, invalid or out-of-range records.
- Convert text values to numeric, date and timestamp types so they can be aggregated in reports.
- Split and combine address columns and build pivot tables from cleaned PostgreSQL data.
Program Overview
String Cleaning and Pattern Matching
Sets out why cleaning belongs early in the pipeline, then works through string functions, pattern matching and similarity checks for values that need fixing.
- Why clean data early in the pipeline
- Trimming spaces and other string functions
- Regular expressions for masking identifying information
- String similarity for inconsistent color names
Missing, Duplicate, and Invalid Records
Queries that measure completeness, fill gaps, and catch repeated, invalid or inconsistent rows in tables of parking violations.
- Completeness checks and fill-in values with COALESCE
- Duplicate parking violation records
- Invalid, out-of-range and inconsistent values
- Recovering deleted data
Type Conversion, Dates, and Pivot Tables
Changes text into numbers, dates and timestamps, then reshapes postal and parking data by combining and splitting columns and building pivot tables.
- Numeric conversion with a CASE clause and aggregates
- Parsing and formatting dates and timestamps
- CONCAT, SUBSTRING and REGEXP_SPLIT_TO_TABLE on address data
- Pivot tables with FILTER
Certificate Awarded (Paid)
Job Outlook
Cleaning data inside a PostgreSQL database is part of data analysis and reporting work wherever SQL tables feed reports or further analysis.
Good for: Analysts working with PostgreSQL tables who want SQL routines for cleaning text, gaps, duplicates and dates before any analysis begins.
Keep in mind: Most exercises draw on New York City parking violation records, so the examples stay within one domain, and every query is written in PostgreSQL syntax.
Bottom line: Its strength is the range of cleaning queries it drills, covering string similarity, completeness checks, date parsing and pivot tables in PostgreSQL.
Frequently Asked Questions
Which PostgreSQL functions and clauses does the course use?
The exercises rely on COALESCE for missing values, regular expressions and similarity functions for strings, CASE for type conversion, CONCAT, SUBSTRING and REGEXP_SPLIT_TO_TABLE for combining and splitting columns, and FILTER for building pivot tables.
Which New York City records do the exercises use?
Most practice uses public records from New York City's Open Data program, chiefly parking violations. The final chapter adds postal data, where addresses are split into city, state and zip code, and one pivot table exercise aggregates film categories.
How does the course handle dates stored as text?
One chapter parses strings into dates and timestamps, formats them for display, cleans invalid dates and extracts hours from time values. The converted values then feed reports such as parking violations by day of the month.


There are no reviews yet.