Lesson Beginner

SQL datatypes

A column name tells you what a value represents.
Its datatype tells the database how that value should behave.

Why Types Matter So Early #

Datatypes affect sorting, filtering, joins, and calculations.
If a column has the wrong type, you can write valid SQL and still get the wrong result.

Classic examples:

Numbers stored as text sort lexicographically ('10' comes before '2'), not numerically.
Date strings do not always behave like DATE or TIMESTAMP values.
Money stored as FLOAT accumulates rounding errors over many rows.

The Types You Will See Every Day #

Type	When to use	Example
`INTEGER`	Whole numbers, counts, IDs	`42`
`BIGINT`	Large counts that exceed `INTEGER` range	`9000000000`
`DECIMAL(p,s)`	Money or anything where precision matters	`19.99`
`VARCHAR`	Variable-length text: names, emails, statuses	`'paid'`
`TEXT`	Long free-form text with no length cap	`'long note...'`
`DATE`	Calendar day, no time	`'2026-02-22'`
`TIMESTAMP`	Date plus time, no timezone (`TIMESTAMPTZ` is the timezone-aware variant)	`'2026-02-22 14:30:00'`
`BOOLEAN`	True/false flags	`true`

Two quick rules that avoid most beginner pain:

Use DECIMAL, not FLOAT, for money.
Use DATE or TIMESTAMP, not VARCHAR, for anything date-shaped.

When You Need Conversion: CAST #

Real datasets are messy.
You will often need to convert one type into another before analysis.
That conversion is called casting.

sql

SELECT
  total,
  CAST(total AS INTEGER) AS total_int
FROM orders
LIMIT 5;

CAST(total AS INTEGER) drops the decimals. A value of 49.99 becomes 49, not 50. Casting truncates, it does not round.

Casting fails when the conversion does not make sense, for example casting the text 'paid' to INTEGER. Always check that the source column can actually represent the target type before casting in production queries.

A Note on NULL #

You will also see NULL in many real columns.
NULL means missing or unknown, and it behaves differently from every other value in this table.
It gets its own lesson: NULL handling (IS NULL, COALESCE).

Try It #

Focus on one thing: casting total to INTEGER and seeing how the value changes.

Loading SQL editor...

Mistakes to Watch For #

Comparing incompatible types without casting (text vs. number).
Storing money in FLOAT, where precision errors accumulate.
Storing dates as VARCHAR, which breaks sorting and arithmetic.
Assuming text sorting behaves like numeric sorting.
Ignoring timezone or granularity differences when working with timestamps.

Knowledge check #

4 questions

0 / 4 answered

Which type should you use for money?
VARCHAR(50) means:
A column declared INT cannot exceed about 2.1 billion (signed). For a primary key on a fast-growing event-style table, you should use:
For a column that represents an event timestamp in UTC, the most defensive type is:

Next Step #

Continue to How to run queries (tooling) to practice writing, running, and debugging SQL in the IDE.