Cleaning Data in PostgreSQL Databases

Start Free Preview

Buying via our links supports this free directory · Affiliate disclosure.
Course facts
  • Payment: Free Preview (Paid Upgrade)
  • Learning style: Self-paced, start anytime
  • Format: Text & Interactive
  • Video access: Streaming Only
  • Before you start: Data Manipulation in SQL
DataCamp details
  • Chapters: 4
Created byDataCamp
Available onDataCamp

Course Details
Learning Style

Start Anytime

Level

Intermediate

Study Mode

100% Online

Duration

Short (< 10 Hours)

Languages
Audio Language

English

Outcomes
Certificate

Certificate of Completion

Price & Terms
Pricing Model

Free Preview (Paid Upgrade)

Technical
Content Medium

Text & Interactive

Video Access

Streaming Only

Specification
Access

Subscription

SKU: cleaning-data-postgresql-databases Category: Tags: , Brand:

Clean messy PostgreSQL tables with SQL: standardize strings, fill missing values, resolve duplicates, convert dates and split address columns.

At a glance
Duration4 hrs
LevelIntermediate
CertificateCertificate of Completion
LanguageEnglish
AccessSubscription
Add your review
Add to wishlistAdded to wishlistRemoved from wishlist 0
Add to compare

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.


Our take

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.

User Reviews

0.0 out of 5
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0

No reviews yet. Be the first to review this course.

There are no reviews yet.

Write your review

Reviews can only be posted by registered learners. Log in, then you will return here to post.

×

    Report this page
    Cleaning Data in PostgreSQL Databases
    Cleaning Data in PostgreSQL Databases
    LiveTalent
    Logo
    Compare items
    • Total (0)
    Compare
    0