Cleaning: types and formats¶

Exercise cleaning-types-formats-annex.pdf · PDF

Quick question: which is bigger, 10 or 9? You did not even have to think. Now ask a database the same thing, with one small change:

SELECT '10' < '9' AS as_text, 10 < 9 AS as_number;

With quotes, 10 is smaller than 9. Without quotes, it is not. Same digits, opposite answers. The quotes turn a number into text, and text is compared like words in a dictionary: 1 comes before 9, end of story. This is exactly why games could not sort its own release dates. On this page you fix it.

The furnace: from ore to ingots¶

In the data quality audit you opened the chest and only looked. Now you light the furnace. In a crafting game, ore looks like metal, but you cannot craft with it until it is smelted into an ingot. '19.99' looks like a number. It is not one yet.

A data type is the kind of value a column holds. It decides which operations make sense: you can add two numbers, you can subtract two dates, you can only glue two texts together.

Normalising a column means giving it one type and one format, so that every value is written in the same way.

We keep the furnace analogy for the whole page: text is the ore, typed values are the ingots.

Casting: lighting the furnace¶

Casting is converting a value from one type to another. The value either comes out in the new type, or the query stops with an error.

PostgreSQL has two spellings for it. They do exactly the same thing:

SELECT '19.99'::numeric + 1, CAST('42' AS integer) * 2;

When the furnace cannot swallow a value, it says so:

SELECT '1,234.50'::numeric;

ERROR:  invalid input syntax for type numeric: "1,234.50"

That comma is the problem. Hold on to this error: we will come back to whether it is good news or bad news.

Trimming: spaces you cannot see¶

Trimming is cutting the spaces off both ends of a text.

A space is a character like any other. For you, ' Valve ' and 'Valve' are the same publisher. For a database they are two different ones:

SELECT ' Valve ' = 'Valve' AS as_typed,
       TRIM(' Valve ') = 'Valve' AS trimmed;

One stray space in a file and your GROUP BY publisher shows the same company twice, with the games split between the two rows. TRIM(x) removes the spaces at the start and at the end. It does not touch the ones in the middle, so 'Half-Life 2' keeps its space.

In games, TRIM(game) changes 0 rows: the names are already tidy. We keep it in the clean query anyway. It costs nothing and it protects you the day somebody uploads a file that is not so tidy.

Separators: decoration for humans¶

A decimal separator marks where the whole part of a number ends. A thousands separator only groups the digits so that people can read them.

Here is the catch: countries disagree on which symbol does which job.

Both lines are the same number: one thousand two hundred and thirty-four and a half. The furnace only takes the third form. So you remove the decoration first, with REPLACE(text, old, new), which swaps every old for new:

SELECT REPLACE('1,234.50', ',', '')::numeric;                      -- 1234.50
SELECT REPLACE(REPLACE('1.234,50', '.', ''), ',', '.')::numeric;   -- 1234.50

The Spanish one needs two steps, and the order matters: first delete the points (thousands), then turn the comma into a point (decimals). Do it the other way round and you delete the decimal point you just created.

Dates: one value, many spellings¶

Dates are the worst offenders, because every country and every website writes them differently. TO_DATE(text, pattern) reads a date written the way the pattern says:

Most dates in games look like Nov 16, 2004, so the pattern is 'Mon DD, YYYY'. Try the lazy way first, on the whole column:

SELECT release_date::date FROM games;

ERROR:  invalid input syntax for type date: "Jun 2009"

SELECT TO_DATE(release_date, 'Mon DD, YYYY') FROM games;

ERROR:  date/time field value out of range: "Jun 2009"

Both lines break on the same kind of value: a date with no day. The audit already warned us that 42 rows had another shape. Before converting, count the shapes exactly.

Count the shapes¶

Two new tools. CASE is a fork in the road: each row takes the first branch whose condition is true (it gets its own lesson in Categorisation). And ~ asks "does this text match this pattern?".

SELECT CASE WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$' THEN 'full'
            WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$'             THEN 'month only'
            ELSE 'other' END AS shape,
       COUNT(*) AS n
FROM games GROUP BY shape ORDER BY n DESC;

You do not need to write these patterns from memory, only to read them:

The single row in other is 8 Oct, 2014, the game Hazard Ops: day first, month second, the opposite of all the others.

Build the converter¶

Same fork, but now each road smelts its own shape. There is no ELSE: whatever fits neither road becomes NULL.

WITH step1 AS (
  SELECT number, TRIM(game) AS game,
    CASE WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$'
           THEN TO_DATE(release_date, 'Mon DD, YYYY')
         WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$'
           THEN TO_DATE(release_date, 'Mon YYYY')
    END AS release_date,
    price::numeric(8,2) AS price,
    owners, developer, publisher, average_playtime, median_playtime, metascore
  FROM games
  WHERE game IS NOT NULL
)
SELECT COUNT(*), MIN(release_date), MAX(release_date),
       COUNT(*) - COUNT(release_date) AS no_date
FROM step1;

Three things are new here:

The last SELECT is a check row: how many rows, the oldest date, the newest date, how many have no date. It reads 26,685 games, from 2004 to 2018, and exactly one without a date. That one is Hazard Ops.

Use it¶

With a real date, a question that was impossible on the raw table takes one line. Keep the WITH block and change only the final SELECT:

SELECT EXTRACT(YEAR FROM release_date)::int AS year, COUNT(*) AS n
FROM step1 GROUP BY year ORDER BY year;

%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#4051b5"}}}}%%
xychart-beta
    title "Games released per year in the class table"
    x-axis [2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]
    y-axis "Games" 0 --> 9000
    bar [53, 58, 112, 143, 183, 334, 340, 364, 426, 556, 1352, 2544, 4503, 6871, 8845]

53 games in 2004, 8,845 in 2018. That curve was hidden inside a text column all along.

Loud errors and silent errors¶

Back to the furnace refusing '1,234.50'. Good news or bad news?

A loud error stops the query and tells you which value it could not convert. A silent error gives you a wrong value and no warning at all.

Good news. A loud error is the easy kind: you see it, you fix it. Now meet the other kind. Take the 4^th of November 2026, written the Spanish way:

SELECT '04/11/2026'::date;                    -- 2026-04-11
SELECT TO_DATE('04/11/2026', 'DD/MM/YYYY');   -- 2026-11-04

The first line returned the 11^th of April. No error. PostgreSQL had to guess, and it guessed month first, the American way. The second line was told the format, so it got it right.

flowchart TB
    T["<b>Text value</b><br>'04/11/2026'"] --> G{"Did you tell<br>the format?"}
    G -- "No: '04/11/2026'::date" --> S["<b>Silent error</b><br>2026-04-11, no warning"]
    G -- "Yes: TO_DATE(..., 'DD/MM/YYYY')" --> OK["<b>Correct</b><br>2026-11-04"]
    S --> CH["Ends up in a chart.<br>Somebody decides with it."]

The rule: when you convert, you tell the furnace the format. You never let it guess. And you always check the result with a check row, even when nothing complained.

Try it: release_date becomes a real date¶

Open SQL → SQL Lab, database Dades classe, schema public. Run one statement at a time: select it, then Run.

Step 1 — Try the lazy way. Run the two lines that fail (release_date::date and TO_DATE with one pattern). You should get two error messages, both naming a value such as "Jun 2009".

Step 2 — Count the shapes. Run the CASE query. You should see full 26,646, month only 41 and other 1. Copy the patterns from this page instead of retyping them: one missing space costs five minutes.

Step 3 — Build the converter. Run the WITH step1 query. The check row must read 26,685 rows, oldest 2004-01-11, newest 2018-12-31, no_date 1. If no_date is 42 or 26,685, a pattern was mistyped.

Step 4 — Use it and save it. Change the final SELECT to the games-per-year query. Then press Save, next to Run, and name the query games_step1. It appears under SQL → Saved Queries. The next lesson starts from it.

Key takeaways¶

• A data type decides what you can do with a column. A number or a date stored as text sorts like a dictionary: '10' < '9' is true.
• Casting (CAST(x AS type) or x::type) converts a value or stops with an error. It changes the query result, never the table.
• TRIM cuts spaces at both ends; REPLACE removes thousands separators and fixes the decimal separator before a cast.
• TO_DATE(text, pattern) reads a date in the format you declare. Different shapes need different patterns, chosen with CASE.
• A loud error stops you. A silent error ('04/11/2026'::date giving 11 April) is worse: nothing warns you.
• Always verify a conversion with a check row: count, MIN, MAX, number of NULL.
• Every cleaning choice (one NULL date, 41 dates set to the 1^st) is a decision to write down.

Quick check¶

Dates and prices are now real values. But owners is still a text range, and 74 game names appear more than once. Next: Cleaning: units and duplicates, where two things that look identical turn out not to be.