Insert, Update, Delete

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.

-- Insert a single user
INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 28);

Here's how it breaks down.

1. INSERT INTO users says which table gets the data
2. (name, email, age) says which columns you're filling in
3. VALUES (...) holds the actual values going in

Inserting Multiple Rows#

You can drop in a bunch of rows at once.

INSERT INTO users (name, email, age)
VALUES
  ('Bob', '[email protected]', 34),
  ('Charlie', '[email protected]', 22),
  ('Diana', '[email protected]', 31);

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.

-- '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.

INSERT INTO users (name, email)
VALUES ('Eve', '[email protected]')
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.

-- Update Bob's email
UPDATE users
SET email = '[email protected]'
WHERE name = 'Bob';

Same idea, three parts.

1. UPDATE users says which table you're changing
2. SET email = '...' says what to change
3. WHERE name = 'Bob' says which rows to change

Updating Multiple Columns#

You can change several columns in one go.

UPDATE users
SET
  name = 'Robert',
  email = '[email protected]',
  age = 35
WHERE id = 2;

Updating with Calculations#

You can even use a column's current value inside the update.

-- 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.

-- 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.

-- 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.

-- 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.

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.

-- 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.