Create, Read, Update and Delete Operations

You ever clean out your garage and find stuff you forgot you had? That old bike, the box of tangled cables, that VHS tape with no label. It’s a mess. Now imagine if you had a system, something to add things neatly, find what you need, update stuff when it changes, and toss what’s no longer useful. That’s exactly what CRUD operations do in a database.

CRUD stands for Create, Read, Update, Delete, the four fundamental operations you’ll perform on data in PostgreSQL. Let’s break it down with some real-world SQL examples and a few extra tricks along the way.

Creating Data (INSERT)

Adding new records to a database is like putting a new item on a well-organized shelf. You need to make sure it’s placed correctly, so you can find it later.

INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

PostgreSQL inserts a new user, storing the name, email, and the current timestamp. Simple. But what if you want to insert multiple records at once? Easy:

INSERT INTO users (name, email, created_at)
VALUES ('Bob', 'bob@example.com', NOW()),
       ('Charlie', 'charlie@example.com', NOW());

Reading Data (SELECT)

Need to find something? This is where SELECT comes in. It’s your “database search engine.”

SELECT * FROM users WHERE email = 'alice@example.com';

Want just names and emails? No need for *.

SELECT name, email FROM users;

And if you want it sorted?

SELECT name, email FROM users ORDER BY name ASC;

Need more control? Try filtering and aggregating:

SELECT COUNT(*) FROM users WHERE email LIKE '%@example.com';

That counts all users with an “example.com” email. PostgreSQL can slice and dice your data any way you want.

Updating Data (UPDATE)

People change. So should your data.

UPDATE users SET email = 'alice.new@example.com' WHERE name = 'Alice';

If you forget the WHERE clause, every row gets updated. That’s like renaming every folder on your desktop to “New Folder”. Not fun.

Need to update multiple fields? No problem:

UPDATE users SET email = 'bob.new@example.com', updated_at = NOW() WHERE name = 'Bob';

Deleting Data (DELETE)

Sometimes, data outlives its usefulness.

DELETE FROM users WHERE email = 'alice.new@example.com';

And if you need a fresh start?

DELETE FROM users;

Careful with that one, it wipes the whole table. Use WHERE unless you enjoy regrets.

Want to clear everything but keep the structure?

TRUNCATE TABLE users;

Unlike DELETE, TRUNCATE is faster and resets auto-increment counters.

Transactions: Keeping Your Data Safe

Imagine you’re moving furniture. You wouldn’t carry everything into a new house and then decide if it fits, you’d plan ahead. Databases do the same thing with transactions.

BEGIN;
UPDATE users SET email = 'temporary@example.com' WHERE name = 'Charlie';
ROLLBACK;

That update never happened. ROLLBACK cancels everything inside the transaction. Want to commit the change instead?

BEGIN;
UPDATE users SET email = 'charlie.final@example.com' WHERE name = 'Charlie';
COMMIT;

Wrapping Up

CRUD operations are the backbone of any database-driven app. Whether you’re building a simple blog or a complex system, mastering these basics ensures you can manage data effectively. And just like organizing your garage, a little structure now saves a lot of headaches later.

For larger applications, you’ll also want to explore indexes, constraints, and advanced querying techniques. But if you get these four operations right, you’re already ahead of the game.