Lesson 2 of 15 · 20 min
Choosing data types
By the end of this lesson you can choose a suitable PostgreSQL data type for numbers, money, text, flags, dates, times and moments, and explain why the alternatives are worse.
Every column has exactly one data type. The type is a promise: PostgreSQL refuses values that don't fit ('next Tuesday' in a date column, 'abc' in an integer column) and gives each type its own behaviour. Numbers add, dates subtract into a number of days, text sorts alphabetically. When you choose a type you are choosing which mistakes become impossible.
The types you will use for nearly everything fit in one table:
| What you store | Use | Avoid |
|---|---|---|
| Counts, ids, quantities | integer (up to about 2.1 billion), bigint for bigger, smallint for small codes | numeric for ids, text for numbers |
| Money, rates, anything that must add up exactly | numeric(p, s), e.g. numeric(12,2) | real, double precision, money |
| Measurements where tiny rounding is fine (sensor readings, scores) | double precision | |
| Names, descriptions, codes | text, or varchar(n) when a maximum length is a real business rule | char(n) |
| Yes/no flags | boolean | 'Y'/'N' text, 0/1 integers |
| Calendar days | date | text like '01/03/2025' |
| Moments in time | timestamptz | timestamp (usually), text |
| Durations | interval | integer minutes with no unit |
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons