Data types & casting: why '10' + 5 sometimes breaks

Every column has a type: it holds text, or numbers, or dates, or true/false values. This matters more than beginners expect, because the database treats each type differently, and mixing them up causes quiet bugs.

🎯 Explain Like I'm Hired A data type is just "what kind of thing is in this column": text, a number, a date, a yes/no. Casting means converting from one type to another. It matters because a number stored as text won't sort or add up correctly. '100' (text) sorts before '9' the way words do in a dictionary, not the way numbers should. Example: if a price column was accidentally stored as text, ORDER BY price gives you a nonsense order until you cast it to a number: ORDER BY price::numeric.

The common types

• text / varchar: words, IDs, anything with letters.
• integer / numeric: whole numbers and decimals. Use numeric for money (no rounding surprises).
• timestamp / date: points in time.
• boolean: true / false.

Casting: converting one type to another

Postgres has a short :: syntax for converting:

SELECT
  '2025-01-15'::date        AS a_real_date,   -- text -> date
  '100'::numeric + 5        AS math_works,    -- text -> number, so you can add
  amount::text              AS amount_as_text
FROM orders;

'100'::numeric turns the text '100' into the number 100 so arithmetic works. Without the cast, some databases error and others behave unpredictably.

The interview tip: integer division

A classic gotcha: dividing two whole numbers throws away the decimal.

SELECT 5 / 2;              -- 2, not 2.5  (whole-number division)
SELECT 5.0 / 2;           -- 2.5          (one side is a decimal)
SELECT 5 / 2::numeric;    -- 2.5          (cast one side)

If a percentage or average comes out suspiciously rounded, this is usually why, so cast one side to a decimal first. This bites almost everyone once.

---

Next: Self-joins without the headache →