Why data types matter

Concepts covered: Basic Types

Every column carries a declared type, and that type is the database's contract about what the column holds: a whole number, a piece of text, a true/false flag, a date. The contract settles 3 things at once. How many bytes each value occupies on disk. Which operations the database will run on the value, since addition is defined for numbers and LENGTH for text. And how 2 values compare, which is what sorting, filtering and joining all come down to.

A count that sorts in the wrong order

A warehouse system writes its stock levels to a CSV file, and the loader creates each column as text because CSV carries no type information. The stock table below keeps that export in qty_text, next to the same 5 counts stored as integers in qty. To a person reading them the values are identical: 9, 10, 100, 25 and 3. Run the 3 tabs and compare how the database treats the 2 columns.

Text sort

ORDER BY on the text column compares characters left to right.

SELECT sku, qty_text
FROM stock
ORDER BY qty_text

Number sort

ORDER BY on the integer column compares magnitude.

SELECT sku, qty
FROM stock
ORDER BY qty

Text filter

A filter on the text column keeps only the SKU whose first character beats '5'.

SELECT sku, qty_text
FROM stock
WHERE qty_text > '5'

Sorting qty_text compares one character at a time, the way a dictionary orders words, so '10' lands before '9' because the character '1' is smaller than '9', and '100' sits between '10' and '25'. The filter is worse: qty_text > '5' returns a single row, and the SKU holding 100 units disappears from a report meant to list well-stocked items. The integer column sorts and filters by magnitude with no surprises, because the database knows the values are numbers.

What a type buys the query

• Storage: an INTEGER occupies 4 bytes; the text '2147483647' occupies 10 plus a length header.
• Legal operations: the database adds numbers, measures text and negates booleans, and rejects the rest.
• Ordering: numbers sort by magnitude, text by character, so 100 sorts after 9 only as a number.
• Comparisons: a filter or join compares values of the same type directly, with no conversion per row.

SQL types fall into 4 families: numeric, character, boolean, and date and time. This lesson gives one section to each of the first 3, through INTEGER, VARCHAR and BOOLEAN, and dates and timestamps have a lesson of their own. Most other types met early on specialize one of these families: BIGINT is a wider integer, TEXT is a VARCHAR without a length ceiling, DECIMAL is a number that keeps its fractional digits exactly. A few, such as JSON, binary and UUID, stand outside all 4 and arrive in later lessons.

Arithmetic on text

'100' + '50' looks like an addition, and the result depends entirely on which database runs it. PostgreSQL refuses to add 2 text values and raises operator does not exist: text + text. MySQL and SQLite convert both sides to numbers without a warning and return 150, which is the right answer today and the wrong answer the day a value like '12 units' arrives and converts to 12 or 0. A revenue column stored as text produces a SUM that either errors or returns a number nobody can trust.

The cheapest moment to catch a mismatch is when the table is created, by reading the column list and asking what each value is. Found later, the same mismatch costs a backfill: every row rewritten with the right type, every downstream table that copied the column rebuilt, and every report that ran in between rechecked.

About this lesson

This section is part of the Data Types: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.