Lesson 2 of 5 · 15–25 minutes

Data types

🏠The everyday picture

Containers in a kitchen: a bottle for oil, a jar for rice, a tray for eggs. Putting rice in the egg tray "works", but causes trouble later.

📘What it really is

Common SQL types (names vary slightly between databases; these are PostgreSQL's):

Text: text for any length (or varchar(n) with a maximum length).
Whole numbers: integer, or bigint for very large values (ids in big tables).
Exact decimals: numeric(10, 2) for exact values like ₹1,234.50. Never float/real for money (they're approximate, Module 21). Many systems store money as whole paise in an integer or bigint.
True/false: boolean.
Dates and times:
date for a calendar date (a birthday);
timestamptz (timestamp with time zone) for a moment in time (when an order was placed). Store moments in UTC and convert to Indian time (IST, UTC+5:30) only when displaying.
Ids: integer with automatic numbering, or uuid (random ids that can't be guessed and can be made anywhere).
JSON: jsonb for genuinely flexible data (Module 51, Lesson 4). Don't hide normal columns inside it.
Phone numbers, PIN codes, Aadhaar-like ids: store as text, not numbers. They're identifiers, not quantities: you never add them, and leading zeros or +91 must be kept.

SQLite is more relaxed: it has only a few storage types and, by default, will store text in an integer column. Declaring a table as STRICT makes it enforce types (Module 59).

🖱️Try it

In DB Browser, create a payments table with: id, customer_id, amount_paise (integer), method (text), paid_at (text holding an ISO timestamp, since SQLite has no timestamp type) and note. Create it with CREATE TABLE ... STRICT in the Execute SQL tab, and try inserting text into amount_paise.

✅Quick check

Should a phone number be stored as a number or text?

Show the answer

Text. It's an identifier; arithmetic is meaningless, and +91 or leading zeros must be kept.

Why store timestamps in UTC?

Show the answer

One consistent reference avoids confusion across time zones and daylight-saving changes; convert only for display.