Simplified
Simplified

L4.1Why every column has a type

YOU

Why every column has a type

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11?
code4 8 1 _1234567890birthday03 / 14 / 200412345678910111213141516171819202122232425262728293031Private accounton or off
type
the kind of value a column holds
usersidemailtextnametextxpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11
booleantruefalseInstagram accountsusernametextprivatebooleanana.paintstrueben_runsfalse
usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11'lots'error'soon'error
nametextxpintegerAda120Ben40120 + 40 = 160nametextxpintegerAda120Ben'lots'120 + 'lots' = ?
usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11
usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11'lots'error'soon'error

Databases · Lesson 4.1

Column Types

Why every column has a type

Simplified
0:00 / 2:33

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?

code4 8 1 _1234567890birthday03 / 14 / 200412345678910111213141516171819202122232425262728293031Private accounton or off

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.

usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11

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.

booleantruefalseInstagram accountsusernametextprivatebooleanana.paintstrueben_runsfalse

So what does Postgres do with a value of the wrong type?

usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11'lots'error'soon'error

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.

nametextxpintegerAda120Ben40120 + 40 = 160nametextxpintegerAda120Ben'lots'120 + 'lots' = ?

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.

Example: Why every column has a type

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.

Question 1

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?

Stopping a name that's too long

usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11
Sign upNameHi! I'm Sam. I've been coding fortwo years, mostly React, and nowI'm learning databases so I canbuild my own apps. I also loveLeaderboard1Hi! I'm Sam. I've been codingfor two years, mostly React,and now I'm learning database…40 XP2Ada120 XP?
Usernameana.paints.the.whole.city.dailyUsername must be 30 characters or fewer.
varchar(n)
text of at most n characters, where you pick the n
usersidintegeremailtextnamevarchar(50)xpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11
limit: 50AlexandriaJosephineMontgomery-WhitfieldHarrison50ChristopherMaximilianMontgomery-WhitfieldJohnson51
limit: 30ana.paints.the.whole.city.202630ana.paints.the.whole.city.daily31
emailtextno limitnamevarchar(50)limit: 50
sign-up pagechange-namepageAda51 charactersusersname varchar(50)
emailtextno limitnamevarchar(50)limit: 50
sign-up pagechange-namepageAda51 charactersusersname varchar(50)

Databases · Lesson 4.2

Column Types

Stopping a name that's too long

Simplified
0:00 / 2:17

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.

Usernameana.paints.the.whole.city.dailyUsername must be 30 characters or fewer.

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.

usersidintegeremailtextnamevarchar(50)xpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11

So what happens to a name that's too long?

limit: 50AlexandriaJosephineMontgomery-WhitfieldHarrison50ChristopherMaximilianMontgomery-WhitfieldJohnson51

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.

limit: 30ana.paints.the.whole.city.202630ana.paints.the.whole.city.daily31

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.

emailtextno limitnamevarchar(50)limit: 50

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.

sign-up pagechange-namepageAda51 charactersusersname varchar(50)

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.

Example: Stopping a name that's too long

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.

Question 1

Say Spotify keeps playlist names in a varchar(100) column.

What is the longest playlist name it can store, in characters?

characters

Question 2

A learner's name is 64 characters long, and the name column is varchar(50).

How many characters over the limit is the name?

characters

Question 3

Say Instagram keeps bios in a varchar(150) column. You paste a bio that's 151 characters long.

What does Postgres do with it?

Question 4

A Postgres table has an email column of type text and a name column of type varchar(50).

How do the two types differ?

Question 5

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?

Question 6

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?

characters

Why money needs exact numbers

usersidintegeremailtextnametextxpintegercreated_attimestamp1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11
Spotify Premium$11.99 / month11.99priceinteger
itemtextpriceintegerphone case4.99cable4.99total: 10real total9.98
numeric(p, s)
an exact number: p digits in all, s after the point
numeric(6, 2)6 digits9999994 before the point2 after9,999.99100000010,000.005 before the point
Spotify Premium$11.99 / month11.99pricenumeric(6, 2)
itemtextpriceintegerphone case5cable5total: 10itemtextpricenumeric(6, 2)phone casecabletotal: 9.98
balancenumeric25.50+ 4.99from a friendbalancenumeric30.49
itemtextpriceintegerphone case5cable5total: 10itemtextpricenumeric(6, 2)phone case4.99cable4.99total: 9.98

Databases · Lesson 4.3

Column Types

Why money needs exact numbers

Simplified
0:00 / 2:42

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?

Spotify Premium$11.99 / month11.99priceinteger12

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.

itemtextpriceintegerphone case5cable5total: 10real total9.98

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.

numeric(6, 2)6 digits9999994 before the point2 after9,999.99100000010,000.005 before 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.

Spotify Premium$11.99 / month11.99pricenumeric(6, 2)11.99

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.

itemtextpriceintegerphone case5cable5total: 10itemtextpricenumeric(6, 2)phone case4.99cable4.99total: 9.98

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.

balancenumeric25.50+ 4.99from a friendbalancenumeric30.49

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.

Example: Why money needs exact numbers

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.

Question 1

Say Uber keeps the fare of each ride in dollars and cents, like 23.47.

Which type keeps the fare exact to the cent?

Question 2

Say Amazon keeps prices in a numeric(8, 2) column.

How many digits does each price have after the point?

digits

Question 3

Say Amazon keeps prices in a numeric(8, 2) column.

How many digits can a price have before the point?

digits

Question 4

Say Etsy keeps a price in an integer column in Postgres, and some code saves 7.25 there.

What does the cell hold?

Question 5

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?

dollars

Question 6

Say Amazon keeps prices in a numeric(6, 2) column. Someone tries to save a TV's price as 12,000.00.

What happens?

Assistant

Highlight lesson text and click Ask AI, or type a question below.