Simplified
Simplified

L1.1Adding a new row

YOU

Adding a new row

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-11Sign upEmailNamegia@simplified.orgGia?
INSERT INTO
adds a new row to a table
INSERT INTOusers
INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia');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-117gia@simplified.orgGia02026-11-05 09:30
INSERT INTO users (email, name)VALUES('gia@simplified.org', 'Gia');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-117Giagia@simplified.org02026-11-05 09:30
INSERT INTO posts (username, caption)VALUES ('sam.codes', 'Beach day');postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day0
INSERT INTOusers (email, name)VALUES ('gia@simplified.org', 'Gia');

Databases · Lesson 1.1

Adding Rows

Adding a new row

Simplified
0:00 / 2:46

So far, every query you've seen has read Simplified's users table.

Like the SELECT that sent back Ada's name and XP, for the email she logs in with.

And not one of them changed users.

And users only has rows because learners signed up.

Say a new learner, Gia, signs up on Simplified at 9:30 am on November 5, 2026.

She types her email, gia@simplified.org, and her name, Gia.

Sign upEmailgia@simplified.orgNameGiausersidemailnamexpcreated_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?

So how does Simplified's server get Gia's row into users?

It sends Postgres a query that starts with INSERT INTO.

INSERT INTO adds a new row to a table.

After it comes the table's name, users.

Then, in brackets, the columns the server has values for: email and name.

Then comes VALUES, with the values themselves, in brackets too.

VALUES gives one value for each column you named, in the same order.

INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia');table1212

So the first value, gia@simplified.org, goes in the first column named, email.

And the second value, Gia, goes in the second column, name.

Both are text, so both go in single quotes, just as in a WHERE.

So what does Gia's new row hold?

INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia');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-117gia@simplified.orgGia02026-11-05 09:30

Postgres adds one new row to users.

Its email is gia@simplified.org, and its name is Gia, from VALUES.

The query never named id, because Postgres generates it: 7, the next number after 6.

It never named xp or created at either, so each one gets its DEFAULT.

So Gia's xp is 0, and her created at is when she signed up: 9:30 am on November 5.

So users now holds seven rows.

But what if the values come in a different order from the columns?

Say the server's VALUES had the name first, then the email.

INSERT INTO users (email, name)VALUES ('Gia', 'gia@simplified.org');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-117Giagia@simplified.org02026-11-05 09:30

Postgres never reads what a value says. It only goes by its place.

So Gia would go in email, and gia@simplified.org would go in name.

Then Gia couldn't log in with her email, because no row's email cell would hold it.

So what happens when you post a photo on Instagram?

Say Instagram keeps posts in a table, one row for each post.

INSERT INTO posts (username, caption)VALUES ('sam.codes', 'Beach day');postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day0

Your username is sam.codes. You type the caption "Beach day", and tap Share.

So Instagram's server might send an INSERT INTO posts, with your username and your caption.

Its database generates the post's id, 81, the next after 80.

The query never named likes, so likes gets its DEFAULT, 0.

So your post shows up on your profile with 0 likes.

So INSERT INTO names the table and the columns, and VALUES gives their values, in the same order.

Example: Adding a new row

Simplified's users table has five columns: id, which Postgres generates; email; name; xp, with a DEFAULT of 0; and created_at, with a DEFAULT of now(). A new learner signs up, and Simplified's server sends Postgres INSERT INTO users (email, name) VALUES ('kai@simplified.org', 'Kai');.

What is the new row's xp?

Explanation

First, the query names only two columns, email and name, so xp is left out.

Then, a column a new row leaves out gets its DEFAULT, and xp's DEFAULT is 0.

Therefore, Kai's xp is 0.

Question 1

Simplified's users table has a created_at column with NOT NULL and a DEFAULT of now(), the time the row is added. Its server sends Postgres INSERT INTO users (email, name) VALUES ('ona@simplified.org', 'Ona');.

Where does the new row's created_at come from?

Three rows in one go

INSERT INTO users (email, name)VALUES('gia@simplified.org', 'Gia');
playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Us
Spotify's serverdatabase1INSERT … VALUES (501, 'Espresso');2INSERT … VALUES (501, 'Birds of a Feather');3INSERT … VALUES (501, 'Not Like Us');
INSERT INTO playlist_songs (playlist_id, song)VALUES(501, 'Espresso')
INSERT INTO playlist_songs (playlist_id, song)VALUES (501, 'Espresso'),(501, 'Birds of a Feather'),(501, 'Not Like Us');playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Us
INSERT INTO messages (chat_id, text)VALUES (42, 'meme.jpg'), (57, 'meme.jpg'), (63, 'meme.jpg');messagesidchat_idtext31242On my way31342meme.jpg31457meme.jpg31563meme.jpg
INSERT INTO playlist_songs (playlist_id, song)VALUES(501, 'Espresso'),(501, 'Birds of a Feather'),(501, 'Not Like Us');playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Us

Databases · Lesson 1.2

Adding Rows

Three rows in one go

Simplified
0:00 / 1:56

So Gia's INSERT added one row: one list of values after VALUES, in brackets.

Now say you select three songs on Spotify, and tap Add to playlist.

Say Spotify keeps which songs are in which playlist in a table, one row for each song.

Your Gym playlist has the id 501, so each new row holds 501 and one song's title.

Three INSERTs would work: one for each song.

But that's three queries, so Spotify's server sends three, and waits on its database three times.

Spotify's serverdatabase1INSERT … VALUES (501, 'Espresso');2INSERT … VALUES (501, 'Birds of a Feather');3INSERT … VALUES (501, 'Not Like Us');

So can one INSERT add all three rows?

Yes: after VALUES, you give one list of values for each row.

A comma goes between the lists.

So the query names the columns once: playlist id and song.

Then come three lists, one for each song, with a comma between each.

INSERT INTO playlist_songs (playlist_id, song)VALUES (501, 'Espresso'),       (501, 'Birds of a Feather'),       (501, 'Not Like Us');1columns1row2row3row

So what happens to the table?

INSERT INTO playlist_songs (playlist_id, song)VALUES (501, 'Espresso'), (501, 'Birds of a Feather'),       (501, 'Not Like Us');playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Us

Postgres adds one row for each list, in the order the lists are written.

Say the last id it handed out in this table was 901.

Espresso's list comes first, so its row gets the next id, 902.

Birds of a Feather comes second, so it gets 903, and Not Like Us gets 904.

So one query added three rows, and your Gym playlist has three new songs.

And how might WhatsApp forward a meme to three chats?

Say WhatsApp keeps messages in a table, one row for each message, with its chat's id.

INSERT INTO messages (chat_id, text)VALUES (42, 'meme.jpg'), (57, 'meme.jpg'), (63, 'meme.jpg');messagesidchat_idtext31242On my way31342meme.jpg31457meme.jpg31563meme.jpg

You forward a meme to three chats: Mom, 42; Sam, 57; and Maya, 63.

That's one message in each chat, so three new rows.

So WhatsApp's server might send one INSERT, with three lists: one for each chat.

Its database adds three rows, and the meme shows up in all three chats.

So one INSERT can add many rows: one list of values for each row.

And Postgres hands out their ids in the order the lists are written.

Example: Three rows in one go

Postgres has handed out ids 1 to 7 in Simplified's users table. Then Simplified's server sends INSERT INTO users (email, name) VALUES ('kai@simplified.org', 'Kai'), ('lee@simplified.org', 'Lee'), ('mo@simplified.org', 'Mo');.

What id does Mo's row get?

Explanation

First, Postgres adds one row for each list, in the order the lists are written: Kai, Lee, then Mo.

Then, it hands out the next ids after 7: Kai gets 8, and Lee gets 9.

Therefore, Mo's row gets id 10.

Question 1

Simplified's users table holds 2,000,000 rows. Its server sends Postgres one INSERT with four lists of values after VALUES, one for each of four new learners.

How many rows does users hold afterwards?

rows

Question 2

Postgres has handed out ids 1 to 20 in a table. Then it gets one INSERT with three lists of values after VALUES, written in this order: Ana's, then Bo's, then Cal's. The INSERT leaves out id.

What id does Cal's row get?

Question 3

Say Spotify's server adds two songs to a playlist with one INSERT: INSERT INTO playlist_songs (playlist_id, song) VALUES ….

Which ending adds both rows?

Question 4

A Simplified developer needs to add two test learners.

Why might they send one INSERT with two lists of values, rather than two INSERTs with one list each?

Question 5

Simplified's users table has an xp column with NOT NULL and a DEFAULT of 0. Its server sends Postgres INSERT INTO users (email, name) VALUES ('kim@simplified.org', 'Kim'), ('lou@simplified.org', 'Lou');.

What XP do the two new rows hold?

Question 6

Simplified's server sends Postgres one INSERT with two lists of values: Lou's list first, then Kim's. The INSERT leaves out id.

Which new row gets the smaller id?

Which id did Postgres pick?

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-117gia@simplified.orgGia02026-11-05 09:30Simplified's serveremail: gia@simplified.orgname: Giaid:?
SELECT id FROM usersWHERE email = 'gia@simplified.org';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-117gia@simplified.orgGia02026-11-05 09:30resultid7
RETURNING
names columns of the new row, for Postgres to send back
INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia')RETURNING id;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-117gia@simplified.orgGia02026-11-05 09:30resultid7
INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia')RETURNING id;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-117gia@simplified.orgGia02026-11-05 09:30resultidcreated_at72026-11-05 09:30
INSERT INTO playlist_songs (playlist_id, song)VALUES (501, 'Espresso'),(501, 'Birds of a Feather'),(501, 'Not Like Us')RETURNING id;playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Usresultid902903904
INSERT INTO orders (item)VALUES ('Headphones')RETURNING id;ordersiditem4470USB-C cable4471Phone case4472Headphonesresultid4472
INSERT INTO posts (username, caption)VALUES ('sam.codes', 'Beach day')RETURNING id;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day0resultid81
INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia')RETURNING id;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-117gia@simplified.orgGia02026-11-05 09:30resultid7

Databases · Lesson 1.3

Adding Rows

Which id did Postgres pick?

Simplified
0:00 / 2:44

So Postgres added Gia's row to users, and generated her id, 7.

Simplified's server needs that 7.

Because its later queries point at Gia's row by its id, like "add 10 XP to the learner with id 7".

But an INSERT on its own sends back no rows.

So the server knows Gia's email and name, but not the id Postgres picked.

Simplified's serveremail: gia@simplified.orgname: Giaid:?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-117gia@simplified.orgGia02026-11-05 09:30

So how does the server learn which id Postgres picked?

It could send a second query: a SELECT for the id in the row with Gia's email.

The email is users' logical key, so only Gia's row passes, and 7 comes back.

But that's a second query, just to read back a row the server added a moment ago.

INSERT INTO users (email, name) VALUES ('gia@simplified.org', 'Gia');SELECT id FROM users WHERE email = 'gia@simplified.org';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-117gia@simplified.orgGia02026-11-05 09:30resultid7

So instead, the server ends the INSERT with RETURNING.

RETURNING names columns of the new row, for Postgres to send back.

So Gia's INSERT ends with RETURNING id.

INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia')RETURNING id;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-117gia@simplified.orgGia02026-11-05 09:30resultid7

Postgres adds Gia's row, and generates her id, 7.

Then it sends back a result, like a SELECT's, with one row and one column: 7.

So the server gets Gia's id from the same query that added her row.

And what if the server needs more than the id?

RETURNING can name several columns, with commas between them.

So RETURNING id and created at sends back 7, and 9:30 am on November 5.

Postgres filled both in itself, so the server didn't know either one.

INSERT INTO users (email, name)VALUES ('gia@simplified.org', 'Gia')RETURNING id, created_at;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-117gia@simplified.orgGia02026-11-05 09:30resultidcreated_at72026-11-05 09:30

And what does RETURNING send back for an INSERT that adds three rows?

Spotify's INSERT for your three songs had three lists of values, so it added three rows.

INSERT INTO playlist_songs (playlist_id, song)VALUES (501, 'Espresso'), (501, 'Birds of a Feather'),       (501, 'Not Like Us')RETURNING id;playlist_songsidplaylist_idsong900501Lose Yourself901501Stronger902501Espresso903501Birds of a Feather904501Not Like Usresultid902903904

Ending it with RETURNING id sends back one row for each new row.

So the result holds three rows: 902, 903 and 904, in the order the lists were written.

So how might Amazon show your order number the moment you pay?

Say Amazon keeps orders in a table, with a generated order number as its primary key.

INSERT INTO orders (item)VALUES ('Headphones')RETURNING id;ordersiditem4470USB-C cable4471Phone case4472Headphonesresultid4472

You tap Place your order, and Amazon's server sends an INSERT for it.

Say the last order number Amazon's database handed out was 4471, so yours gets 4472.

The INSERT ends with RETURNING id, so 4472 comes straight back.

So the confirmation page can show order 4472 at once.

And how might Instagram give you a link to the post you just shared?

Say Instagram's posts table generates an id for every new post.

INSERT INTO posts (username, caption)VALUES ('sam.codes', 'Beach day')RETURNING id;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day0resultid81/p/81

You share "Beach day", and Instagram's server sends an INSERT ending with RETURNING id.

The id 81 comes back, from the same query.

So Copy link works at once, with 81 in the link.

So RETURNING sends back the new row's values, from the same query that added it.

Example: Which id did Postgres pick?

Postgres has handed out ids 1 to 9 in Simplified's users table. Its server sends INSERT INTO users (email, name) VALUES ('kai@simplified.org', 'Kai') RETURNING id, xp;. The xp column has a DEFAULT of 0.

What does the result hold?

Explanation

First, Postgres adds Kai's row, with the next id after 9, which is 10, and xp's DEFAULT, 0.

Then, RETURNING sends back the columns it names, id and xp, from that new row.

Therefore, the result holds one row: 10 and 0.

Question 1

Postgres has handed out ids 1 to 41 in a table. Then it gets an INSERT that adds one row, leaves out id, and ends with RETURNING id.

What number does it send back?

Question 2

Simplified's server sends Postgres an INSERT that ends with RETURNING id.

What does RETURNING id do?

Question 3

Simplified's users table has columns id (generated), email, name, xp (DEFAULT 0) and created_at. Postgres has handed out ids 1 to 14. Its server sends INSERT INTO users (email, name) VALUES ('lou@simplified.org', 'Lou') RETURNING name, xp;.

What comes back?

Question 4

Simplified's server sends INSERT INTO users (email, name) VALUES ('ona@simplified.org', 'Ona');, with no RETURNING. Every learner's email is different.

How can the server find the id Postgres gave Ona's row?

Question 5

Postgres has handed out ids 1 to 7 in Simplified's users table. Its server sends one INSERT with two lists of values, Hal's then Jo's, ending with RETURNING id.

What is the larger of the two ids that come back?

Question 6

Simplified's server adds a new learner's row, and uses RETURNING to get its generated id. Later, the learner finishes a lesson, and the server adds XP to their row.

Why does the server want the id?

Assistant

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