You can build tables and you can query them. Now for the other three moves you'll use constantly... adding new data, changing data that's already there and removing data. Put those together with SELECT and you've got the four CRUD operations (Create, Read, Update, Delete) that every single app needs.
Inserting Data with INSERT INTO
To add a new row to a table, you use INSERT INTO.
Example
-- Insert a single user
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 28);
Here's how it breaks down.
INSERT INTO users says which table gets the data
(name, email, age) says which columns you're filling in
That's a lot faster than three separate INSERT statements, since the database only has to process one command.
Inserting with Defaults
If a column has a DEFAULT value, you can just skip it.
Example
-- 'created_at' and 'published' have defaults, so we skip them
INSERT INTO blog_posts (title, slug, body, author_id)
VALUES ('My First Post', 'my-first-post', 'Hello world!', 1);
The created_at column gets set to the current time on its own, and published lands as FALSE.
Getting the Inserted ID Back
In PostgreSQL you can grab the auto-generated ID right after inserting.
Example
INSERT INTO users (name, email)
VALUES ('Eve', 'eve@example.com')
RETURNING id;
-- Returns: id = 5
The RETURNING clause is SUPER handy whenever you need the new record's ID right away (like sending someone to /users/5 after they sign up).
Updating Data with UPDATE
To change rows that already exist, you use UPDATE.
Example
-- Update Bob's email
UPDATE users
SET email = 'bob.new@example.com'
WHERE name = 'Bob';
Same idea, three parts.
UPDATE users says which table you're changing
SET email = '...' says what to change
WHERE name = 'Bob' says which rows to change
Updating Multiple Columns
You can change several columns in one go.
Example
UPDATE users
SET
name = 'Robert',
email = 'robert@example.com',
age = 35
WHERE id = 2;
Updating with Calculations
You can even use a column's current value inside the update.
Example
-- Increment the view count by 1
UPDATE blog_posts
SET view_count = view_count + 1
WHERE id = 42;
-- Give everyone a birthday (increase age by 1)
UPDATE users
SET age = age + 1;
Deleting Data with DELETE
To remove rows from a table, you use DELETE FROM.
Example
-- Delete a specific user
DELETE FROM users WHERE id = 3;
-- Delete all unpublished posts
DELETE FROM blog_posts WHERE published = FALSE;
A Safe Delete Pattern
People who've been burned before tend to look at what they're about to delete first.
Example
-- Step 1: Check what will be deleted
SELECT * FROM users WHERE age < 18;
-- Step 2: If the results look right, delete them
DELETE FROM users WHERE age < 18;
Introduction to Transactions
So what happens when you've got several operations that depend on each other? Think about moving money between two bank accounts.
Example
-- Without transactions (DANGEROUS!)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- What if the server crashes here? Money disappeared!
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
Transactions bundle operations together so they ALL succeed or ALL fail.
Example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If anything breaks between BEGIN and COMMIT, the database rolls back every change automatically, like nothing ever happened. That's called atomicity, and it's one of the most important things a database does for you.
Common Patterns
Here are a few real-world patterns you'll lean on a lot.
Example
-- Soft delete (mark as deleted instead of actually removing)
UPDATE users SET deleted_at = NOW() WHERE id = 5;
-- Upsert (insert or update if exists) - PostgreSQL syntax
INSERT INTO user_preferences (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = 'dark';
-- Conditional update
UPDATE orders
SET status = 'shipped'
WHERE status = 'processing' AND created_at < NOW() - INTERVAL '24 hours';
TL;DR
INSERT INTO adds new rows, and RETURNING hands you the new ID back
UPDATE SET changes existing rows, so always include WHERE or you'll update everything
DELETE FROM removes rows, so always include WHERE or you'll delete everything
Transactions (BEGIN/COMMIT) group operations so they all succeed or all fail together
Checking first (a SELECT before your DELETE or UPDATE) saves you from a lot of mistakes
Soft deletes (setting a deleted_at timestamp) are usually safer than hard deletes
What's Next?
You can add, change and remove data now. Next up you'll get WAY more precise about finding it, with filtering and sorting tools like LIKE, IN, BETWEEN and pagination with LIMIT and OFFSET.
