SQL commands come in two groups. DDL (Data Definition Language) builds and changes the structure: schemas, tables, columns, types, views. DML (Data Manipulation Language) works on the rows: insert, update, delete, select.
That's the definition. It didn't mean much to me until I had to clean a real file. So this article uses that file: a hotel's booking export, 286 rows, 20 columns, and dirty in almost every one of them. Names in capitals, phone numbers with +254 and dashes, mpesa next to M-Pesa, guest ratings of 0 and 6, one duplicate booking, and dates in four different formats.
I'll show which commands were DDL, which were DML, and where I got stuck.
Common commands, in one place
DDL: CREATE, ALTER, DROP, TRUNCATE.
DML: INSERT, UPDATE, DELETE, and SELECT (some people put SELECT in its own group, DQL; I treat it as DML because it works on rows).
The rest of this article is those eight commands doing actual work.
DDL: building the tables
The staging table
The first thing I created was a schema and a table where every column is TEXT:
CREATE SCHEMA tembo_hotel;
SET search_path TO tembo_hotel;
CREATE TABLE staging_bookingss (
booking_id TEXT,
guest_name TEXT,
guest_phone TEXT,
guest_city TEXT,
guest_nationality TEXT,
room_no TEXT,
room_type TEXT,
room_rate_per_night TEXT,
check_in_date TEXT,
check_out_date TEXT,
nights_stayed TEXT,
staff_name TEXT,
staff_department TEXT,
staff_salary TEXT,
payment_method TEXT,
booking_status TEXT,
total_amount TEXT,
service_used TEXT,
service_price TEXT,
guest_rating TEXT
);
(Yes, staging_bookingss with two s's. That typo is in my script and I never renamed it. It doesn't matter as long as you're consistent.)
Why TEXT for everything? Because check_in_date contained 28-01-24, total_amount contained KES 34000, and nights_stayed contained -3. If I had declared those as DATE, NUMERIC and INTEGER, the CSV import would have failed on the first bad value. TEXT accepts anything. Load first, clean second, convert last.
My first mistake happened right here, before any data was loaded. I wrote CREATE SCHEMA tembo_resort; and then SET search_path TO tembo_hotel;. Two different names. The CREATE TABLE that followed failed with ERROR: no schema has been selected to create in, which doesn't say "your schema names don't match" but means exactly that. Check the names before anything else.
A working copy
After importing the CSV into the staging table, I made a copy to clean:
CREATE TABLE cleaning_bookings AS
SELECT * FROM staging_bookingss;
This is DDL (it creates a table) and DML (it fills it with a SELECT) in one line. I did it so the raw import stays untouched. That decision saved me later, when I broke a date column and needed the original values back. Instead of re-importing the CSV, I copied the column across from the staging table with one UPDATE.
The other DDL commands
I didn't need ALTER on this project, but you will on most. It changes a table that already has data in it:
ALTER TABLE cleaning_bookings RENAME COLUMN room_no TO room_number;
ALTER TABLE cleaning_bookings ADD COLUMN loaded_at TIMESTAMP DEFAULT NOW();
DROP TABLE removes a table completely. TRUNCATE TABLE keeps the table and deletes all rows, which is faster than DELETE with no WHERE. All four are DDL: they change what exists, not what a row says.
DML: cleaning the rows
Look first
Before changing anything I ran a SELECT DISTINCT on every text column. This is the room_type column as it arrived:
SELECT room_type, COUNT(*)
FROM cleaning_bookings
GROUP BY room_type
ORDER BY COUNT(*) DESC;
Eight spellings for four room types: Standard, standard, Std, Deluxe, deluxe, DLX, Suite, Penthouse. Any GROUP BY on this column would have given eight groups instead of four.
The same check on other columns found mpesa (15 rows) next to M-Pesa (59), checked out (15) next to Checked Out (239), and guest ratings of 0 (6 rows), 6 (8 rows) and one blank.
UPDATE, with a WHERE
UPDATE cleaning_bookings
SET room_type = CASE
WHEN UPPER(TRIM(room_type)) IN ('DLX', 'DELUXE') THEN 'Deluxe'
WHEN UPPER(TRIM(room_type)) IN ('STD', 'STANDARD') THEN 'Standard'
ELSE INITCAP(TRIM(room_type))
END;
After this, the same GROUP BY returns four rows: Standard 114, Deluxe 90, Suite 56, Penthouse 25.
The rule I follow for every UPDATE: write it as a SELECT first, look at the rows, then change the word SELECT to UPDATE. An UPDATE with no WHERE clause changes every row, and there is no undo button in pgAdmin. For the name column that looked like this:
-- look
SELECT guest_name FROM cleaning_bookings
WHERE guest_name <> INITCAP(TRIM(guest_name));
-- then change
UPDATE cleaning_bookings
SET guest_name = INITCAP(TRIM(guest_name))
WHERE guest_name <> INITCAP(TRIM(guest_name));
The hard part: dates in four formats
Every other column took me one or two statements. The two date columns took longer than all the others together. Here is what check_in_date looked like when I filtered for anything that wasn't already YYYY-MM-DD:
SELECT booking_id, check_in_date, check_out_date, nights_stayed
FROM cleaning_bookings
WHERE check_in_date !~ '^\d{4}-\d{2}-\d{2}$'
ORDER BY booking_id;
44 rows (one booking, BK0006, is in there twice; more on that below), and four patterns in them:
10/01/2024 slashes, day first
28-01-24 dashes, two-digit year
01-12-2024 dashes, four-digit year, month first
15-11-2024 dashes, four-digit year, day first
What I did first, and why it broke
My first idea was to strip everything that isn't a digit, so all the dates become one shape, then convert once:
UPDATE cleaning_bookings
SET check_in_date = TRIM(REGEXP_REPLACE(check_in_date, '[^0-9]', '', 'g'));
UPDATE cleaning_bookings
SET check_in_date = TO_DATE(check_in_date, 'YYYYMMDD');
The second statement failed:
ERROR: date/time field value out of range: "111023"
111023 used to be 11-10-23. Now it's six digits, and YYYYMMDD reads it as year 1110, month 23. The eight-digit ones were no better: 10012024 (from 10/01/2024) would be read as the year 1001. And even with the right format string, 01122024 can't tell you whether it was 01-12-2024 (12 January in US style) or 01/12/2024 (1 December).
The separators were the only thing telling me which format each value was in. The first UPDATE destroyed them. The only way back was the untouched staging table:
UPDATE cleaning_bookings cb
SET check_in_date = sb.check_in_date
FROM staging_bookingss sb
WHERE cb.booking_id = sb.booking_id;
That's the moment the working copy paid for itself.
What worked: one format at a time
Use the separator and the length to identify each format, and convert only those rows:
-- slashes: DD/MM/YYYY
UPDATE cleaning_bookings
SET check_in_date = TO_DATE(check_in_date, 'DD/MM/YYYY')::TEXT
WHERE check_in_date LIKE '%/%';
-- dashes, 8 characters: DD-MM-YY
UPDATE cleaning_bookings
SET check_in_date = TO_DATE(check_in_date, 'DD-MM-YY')::TEXT
WHERE check_in_date LIKE '%-%' AND LENGTH(check_in_date) = 8;
-- dashes, 10 characters, first part bigger than 12: must be DD-MM-YYYY
UPDATE cleaning_bookings
SET check_in_date = TO_DATE(check_in_date, 'DD-MM-YYYY')::TEXT
WHERE check_in_date ~ '^\d{2}-\d{2}-\d{4}$'
AND SPLIT_PART(check_in_date, '-', 1)::INTEGER > 12;
-- dashes, 10 characters, whatever is left: MM-DD-YYYY
UPDATE cleaning_bookings
SET check_in_date = TO_DATE(check_in_date, 'MM-DD-YYYY')::TEXT
WHERE check_in_date ~ '^\d{2}-\d{2}-\d{4}$';
Two things to notice. Each UPDATE turns its rows into YYYY-MM-DD, which is 10 characters with dashes, so the later rules have to be written so they don't grab rows that are already clean (the ^\d{2}-\d{2}-\d{4}$ pattern does that: a clean date starts with four digits, not two). And the order matters: the "first part bigger than 12" rule has to run before the catch-all.
Then the same four statements for check_out_date. All eight ran in one go:
And the check:
SELECT booking_id, check_in_date, check_out_date
FROM cleaning_bookings
WHERE check_in_date !~ '^\d{4}-\d{2}-\d{2}$'
OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$';
Zero rows.
The ambiguous ones
That last rule, "whatever is left is MM-DD-YYYY", was a guess. 01-12-2024 could be 12 January or 1 December. I didn't want to guess, so I used the other columns to check. Every booking has a check-out date and a nights_stayed. If I read the date correctly, check-out minus check-in should equal nights stayed:
SELECT booking_id, check_in_date, check_out_date, nights_stayed,
check_out_date::DATE - check_in_date::DATE AS diff
FROM cleaning_bookings
WHERE check_out_date::DATE - check_in_date::DATE <> nights_stayed::INTEGER;
For BK0007, 01-12-2024 to 01-17-2024 with 5 nights only works as 12 January to 17 January. Month first. Every dash-and-four-digit-year row in the file followed that pattern except BK9005 (15-11-2024), which the "bigger than 12" rule had already caught.
The query returned one row I wasn't looking for: BK9002, check-in 2024-09-10, check-out 2024-09-08. A check-out two days before check-in. The dates were in the right format; the data was still wrong. Formatting and correctness are two separate checks.
DELETE: the duplicate
Back to BK0006, the booking that showed up twice in the date list. A production table needs one row per booking, so before moving anything across I checked how many were duplicated:
SELECT booking_id, COUNT(*)
FROM cleaning_bookings
GROUP BY booking_id
HAVING COUNT(*) > 1;
One result: BK0006, twice. Both rows were identical in all 20 columns, so there was no column I could use in a WHERE clause to delete just one of them. PostgreSQL has a hidden column called ctid that holds each row's physical position, and it's different even for identical rows:
DELETE FROM cleaning_bookings
WHERE ctid = (
SELECT ctid
FROM cleaning_bookings
WHERE booking_id = 'BK0006'
LIMIT 1
);
286 rows became 285.
Back to DDL: the production table
Once every column was clean text, I created the real table with real types and constraints:
CREATE TABLE bookings (
booking_id VARCHAR(10) PRIMARY KEY,
guest_name VARCHAR(100),
guest_phone VARCHAR(15),
room_type VARCHAR(20) CHECK (room_type IN ('Standard', 'Deluxe', 'Suite', 'Penthouse')),
room_rate_per_night NUMERIC(10,2),
check_in_date DATE,
check_out_date DATE,
nights_stayed INTEGER,
payment_method VARCHAR(20),
booking_status VARCHAR(20),
total_amount NUMERIC(12,2),
guest_rating INTEGER CHECK (guest_rating BETWEEN 1 AND 5)
-- staff and service columns left out here to keep the example short
);
and moved the data across with one DML statement:
INSERT INTO bookings
SELECT booking_id,
guest_name,
NULLIF(guest_phone, ''),
room_type,
room_rate_per_night::NUMERIC,
check_in_date::DATE,
check_out_date::DATE,
nights_stayed::INTEGER,
payment_method,
booking_status,
NULLIF(REGEXP_REPLACE(total_amount, '[^0-9.]', '', 'g'), '')::NUMERIC,
NULLIF(guest_rating, '')::INTEGER
FROM cleaning_bookings;
This is where DDL and DML meet. The CREATE TABLE sets the rules. The INSERT is the moment the text gets cast into dates and numbers, and if anything is still dirty the whole INSERT fails and tells you which value. Run it before fixing the ratings and you get:
ERROR: new row for relation "bookings" violates check constraint "bookings_guest_rating_check"
DETAIL: Failing row contains (BK0258, Rachel Mwenda, ..., 0).
The CHECK constraint doing its job. I set the 15 invalid ratings (0, 6 and the blank) to NULL in the cleaning table and ran the INSERT again: 285 rows, every one of them typed.
What I'd tell someone doing this for the first time
Keep the raw import untouched and clean a copy. Run SELECT DISTINCT on every column before you write a single UPDATE. Write every UPDATE as a SELECT first. For dates, never strip the separators before you've used them to identify the format. Convert one format at a time, then run a query that should return zero rows. And when the format is ambiguous, look for another column that can settle it. In this file it was nights_stayed.
DDL built the containers: two schemas' worth of tables, one of them typed and constrained. DML did everything in between. Knowing which command belongs to which group tells you what it can break: DDL breaks structure, DML breaks data. Both are easy to recover from if you kept a copy.
Top comments (0)