Databases · Lesson 4.1
Why every column has a type
When a learner signs up for Simplified, Postgres adds a row for them to its users table.
Every row has the same five columns: id, email, name, xp and created at.
So Ada's row holds her email, ada@simplified.org, and her XP, 120.
Say Simplified adds up the XP in that column, like Ada's 120 and Ben's 40.
That only works if every cell in xp holds a number.
But what stops a word from ending up in an xp cell?
Well, you've seen forms that only take one kind of value.
Tap a field for a code, and you get a number keypad, with no letters.
Tap the field for your birthday, and you get a date picker.
And Instagram's "Private account" setting is a switch, either on or off.
Postgres does the same thing, for every column.
Each column has a type.
A type is the kind of value a column holds.
text holds any characters. email and name are text.
integer holds a whole number. id and xp are integer.
timestamp holds a date and a time. Created at is one, for when a learner signed up.
boolean holds true or false, and nothing else.
users keeps no on-or-off facts, so it has no boolean column.
But Instagram might keep its "Private account" switch in a boolean column.
On is true, and off is false.
So what does Postgres do with a value of the wrong type?
Say some code tries to save the word 'lots' as Ben's XP.
Postgres checks the value against the column's type.
xp is integer, and 'lots' isn't a whole number.
So Postgres refuses it, with an error.
Nothing is stored, so Ben's xp cell still holds 40.
Or say some code tries to save the word 'soon' as Cy's created at.
A timestamp needs a date and a time, and 'soon' is neither.
So Postgres refuses that too, and Cy's cell still holds February 14, 2026.
That's what keeps Simplified's sum of XP working.
Ada's 120 and Ben's 40 add up to 160.
If Ben's cell held 'lots', 120 plus 'lots' wouldn't be a number at all.
Because xp is integer, every cell in it is a whole number, so the sum always works.
So every column has a type, and each of its cells holds that kind of value.
Postgres refuses a value of another kind, so a column never holds a mix.
Say Duolingo keeps each learner's streak in an integer column. Your streak is 12. Some code tries to save the word 'twelve' in its place.
What does your streak cell hold afterwards?
Explanation
First, the streak column is integer, and 'twelve' is a word, not a whole number.
Then, Postgres refuses the value with an error, so nothing is stored.
Therefore, your streak cell still holds 12.
Say YouTube keeps each video's views in an integer column in Postgres, and adds the column up to get a channel's total views. Some code tries to save the word 'viral' in one video's views cell.
Why does the total still come out as a number?
Databases · Lesson 4.2
Stopping a name that's too long
Ada's name is text, and text holds any characters.
But any characters means any number of them, too.
Say someone signs up for Simplified and pastes a whole paragraph as their name.
Then every page that shows their name would show that paragraph.
So how do you stop a name from being too long?
Well, you've seen apps set a limit.
Type a username on Instagram that's too long, and a message comes up.
It says "Username must be 30 characters or fewer."
Instagram won't take the username until you make it shorter.
Postgres can put a limit like that on a column.
This is called varchar(n).
varchar(n) is text of at most n characters, where you pick the n.
So Simplified's name column is varchar(50), not plain text.
A name can be at most 50 characters long.
So what happens to a name that's too long?
Say a learner's full name is 50 characters long, counting the spaces.
Postgres counts the characters before it stores the name.
50 isn't over the limit of 50, so the name is stored.
Now say another learner's name is 51 characters long.
Postgres counts 51, one over the limit.
So it refuses the name, with an error, and nothing is stored.
Instagram might keep its usernames the same way, in a varchar(30) column.
A username of 30 characters fits, so Postgres stores it.
A username of 31 characters is one over, so Postgres refuses it.
So how is varchar(50) different from text?
In Postgres, both store text in the same way.
The only difference is the limit: text has none.
But if the sign-up page can check a name's length, why put the limit in the table?
Because more than one piece of code can write to the same table.
Say Simplified's sign-up page checks that a name is 50 characters or fewer.
Then a developer adds a page for changing your name, and forgets that check.
The limit is in the table, so Postgres checks every name it's sent, from either page.
So a 51-character name from the new page is refused too.
So varchar(50) is text that's at most 50 characters long.
And the limit lives in the table, so Postgres refuses a longer name from any code.
Say TikTok keeps bios in a varchar(80) column. You write a bio that's exactly 80 characters long.
Is that bio stored or refused?
Explanation
First, varchar(80) holds text of at most 80 characters.
Then, your bio is 80 characters, which isn't over the limit.
Therefore, the bio is stored.
Say Spotify keeps playlist names in a varchar(100) column.
What is the longest playlist name it can store, in characters?
A learner's name is 64 characters long, and the name column is varchar(50).
How many characters over the limit is the name?
Say Instagram keeps bios in a varchar(150) column. You paste a bio that's 151 characters long.
What does Postgres do with it?
A Postgres table has an email column of type text and a name column of type varchar(50).
How do the two types differ?
Say Spotify's website and its iPhone app both save playlist names into one Postgres table, where the name column is varchar(100). The website checks the length, but the iPhone app doesn't. Someone types a 120-character playlist name in the iPhone app.
What happens?
Say Threads keeps posts in a varchar(500) column. Your draft is 530 characters long.
At least how many characters must you delete before Postgres will store it?
Databases · Lesson 4.3
Why money needs exact numbers
On Simplified, a lesson is worth a whole number of XP, like 10 or 20.
So xp is integer, and Ada's XP is a whole number, 120.
But other apps keep money, and money comes with cents.
Spotify Premium, for example, costs $11.99 a month in the US.
So what happens to $11.99 in an integer column?
Say Spotify kept its price in an integer column.
An integer holds whole numbers only, and 11.99 isn't one.
But 11.99 is still a number, so Postgres doesn't refuse it, the way it refused 'lots'.
Instead, it rounds it to the nearest whole number: 12.
So the price would be saved as $12, a cent too much.
And those cents add up.
Say your Amazon basket holds a $4.99 phone case and a $4.99 cable.
In an integer column, each price is rounded to 5.
So the basket's total comes out as 10.
But the real total is 9.98, so you'd pay 2 cents too much.
So how do you keep money exact, to the cent?
Well, Postgres has a type that keeps numbers exactly, cents and all.
It's called numeric.
numeric(p, s) is an exact number with p digits in all, and s after the point.
Take numeric(6, 2).
The 6 means 6 digits in all.
The 2 means 2 of those digits come after the point.
That leaves 4 digits before the point.
So the biggest number it holds is 9,999.99.
A bigger number, like 10,000, needs 5 digits before the point.
So Postgres refuses it, with an error.
Now say Spotify keeps its price in a numeric(6, 2) column.
11.99 has 2 digits before the point and 2 after, so it fits.
Nothing is rounded, so the price is stored as 11.99, exactly.
Say Amazon keeps your basket's prices in numeric(6, 2) columns too.
Each price is stored as 4.99, exactly.
So the total is 4.99 plus 4.99, which is 9.98, to the cent.
And say Venmo keeps your balance in a numeric column, and it holds 25.50.
A friend sends you $4.99.
Venmo adds 4.99 to 25.50, and your balance is exactly 30.49.
So an integer column rounds the cents away, but numeric keeps every one.
That's why money goes in a numeric column.
Say Spotify keeps a price in a numeric(5, 2) column.
What is the biggest price it can hold?
Explanation
First, numeric(5, 2) has 5 digits in all, and 2 of them after the point.
Then, that leaves 3 digits before the point, and the biggest digit is 9.
Therefore, the biggest price is 999.99.
Say Uber keeps the fare of each ride in dollars and cents, like 23.47.
Which type keeps the fare exact to the cent?
Say Amazon keeps prices in a numeric(8, 2) column.
How many digits does each price have after the point?
Say Amazon keeps prices in a numeric(8, 2) column.
How many digits can a price have before the point?
Say Etsy keeps a price in an integer column in Postgres, and some code saves 7.25 there.
What does the cell hold?
Say Amazon keeps prices in a numeric(6, 2) column, and your basket holds three items at $4.99 each.
What is the basket's total, in dollars?
Say Amazon keeps prices in a numeric(6, 2) column. Someone tries to save a TV's price as 12,000.00.
What happens?