Messy customers sample database for PostgreSQL
A ready-to-run PostgreSQL script with a customer list with duplicates, blanks, mixed dates and text in number columns. Tested on PostgreSQL 15.18. Updated 19 November 2026.
Messy customers database for PostgreSQL
The customer table with problems added on purpose: repeated customer IDs, names in the wrong case, trailing spaces, blank emails, phone numbers in three formats, state names mixed with codes, dates in three formats, and $ signs or N/A in the spend column.
This page has it as a PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the messy customers dataset page, where it is also available as CSV and Excel.
We loaded this exact file into PostgreSQL 15.18 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
psql
createdb practice
psql -d practice -f messy-customers-postgresql.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
messy_customers 1,000 rows
| Column | PostgreSQL type |
|---|---|
| customer_id | VARCHAR(10) |
| first_name | VARCHAR(20) |
| last_name | VARCHAR(20) |
| VARCHAR(40) | |
| phone | VARCHAR(20) |
| city | VARCHAR(20) |
| state | VARCHAR(20) |
| signup_date | VARCHAR(20) |
| segment | VARCHAR(20) |
| total_orders | VARCHAR(10) |
| total_spent | VARCHAR(20) |
| notes | VARCHAR(20) |
Practice questions
answers from PostgreSQL 15.18
How many rows and how many distinct customer_id values are there? (duplicates show up as the difference)
SQL
SELECT COUNT(*) AS row_count, COUNT(DISTINCT customer_id) AS distinct_ids
FROM messy_customers;Result
| row_count | distinct_ids |
|---|---|
| 1000 | 960 |
How many rows have a missing or blank email?
SQL
SELECT COUNT(*) AS blank_emails
FROM messy_customers
WHERE (email IS NULL OR TRIM(email) = '');Result
| blank_emails |
|---|
| 44 |
After lower-casing and trimming, how many distinct emails remain, compared with the raw values?
SQL
SELECT COUNT(DISTINCT email) AS raw_distinct, COUNT(DISTINCT LOWER(TRIM(email))) AS cleaned_distinct
FROM messy_customers
WHERE NOT (email IS NULL OR TRIM(email) = '');Text comparison is case-sensitive by default, so upper- and lower-case versions of an email count separately.
Result
| raw_distinct | cleaned_distinct |
|---|---|
| 838 | 827 |
Which state values are not a clean two-letter code? List them with counts.
SQL
SELECT state, COUNT(*) AS n
FROM messy_customers
WHERE LENGTH(state) <> 2
GROUP BY state
ORDER BY n DESC, state;LENGTH counts characters.
Result
| state | n |
|---|---|
| New York | 13 |
| Texas | 12 |
| Florida | 8 |
| California | 7 |
| Illinois | 7 |
| Pennsylvania | 5 |
| Georgia | 4 |
| Indiana | 4 |
| Massachusetts | 4 |
| Nevada | 4 |
| Ohio | 4 |
| Oregon | 4 |
| Utah | 3 |
| Wisconsin | 3 |
First 14 of 26 rows.
How many signup dates are not in ISO format (YYYY-MM-DD)?
SQL
SELECT COUNT(*) AS non_iso_dates
FROM messy_customers
WHERE signup_date NOT LIKE '____-__-__';Result
| non_iso_dates |
|---|
| 225 |
How many rows have a blank total_orders value?
SQL
SELECT COUNT(*) AS blank_total_orders
FROM messy_customers
WHERE (total_orders IS NULL OR TRIM(total_orders) = '');Result
| blank_total_orders |
|---|
| 69 |
PostgreSQL differences used here
- Text comparison is case-sensitive by default, so upper- and lower-case versions of an email count separately.
- LENGTH counts characters.