Learning PostgreSQL Through Real-World Problem Solving

Learning PostgreSQL Through Real-World Problem Solving (Beginner-Friendly Guide)

If you’ve ever tried learning databases and felt overwhelmed by theory, you’re definitely not alone. A lot of tutorials jump straight into complex terminology — and that can make PostgreSQL feel harder than it really is.

So instead of memorizing syntax, let’s learn PostgreSQL the way real developers do:

👉 by solving actual problems.

To make things easier, imagine we are building a small online store. We’ll use three simple tables:

• users → stores customer data

• products → stores available products

• orders → stores purchase records

That’s it. Nothing complicated. Now let’s walk through real situations you’d actually face.

---

Problem #1 — How Do We Create Clean and Scalable Tables?

Before writing any fancy queries, the first challenge is designing tables that are easy to understand and safe to use long-term.

Here’s a simple setup:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL,
  price NUMERIC(12,2) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  created_at TIMESTAMP DEFAULT NOW()
);

A few important things worth noting:

✔ SERIAL automatically generates IDs
✔ CHECK prevents negative quantities
✔ UNIQUE ensures email isn’t duplicated
✔ REFERENCES links tables together

Good structure = fewer headaches later.

---

Problem #2 — How Do We Find How Much Each User Has Spent?

Now suppose you want to see which users spend the most money in your store. This is useful for analytics, marketing, loyalty programs, and reporting.

Here’s a simple query that does the job:

SELECT 
  u.name,
  SUM(p.price * o.quantity) AS total_spent
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
GROUP BY u.name
ORDER BY total_spent DESC;

What’s happening here?

• We join the tables together

• Multiply price × quantity

• Use SUM() to total spending

• Sort from highest spender to lowest

Clear and powerful.

---

Problem #3 — Why Are My Queries Getting Slow?

This is something that almost everyone experiences.

At the beginning, everything runs fast. Then as your data grows:

❌ queries take longer
❌ reports freeze
❌ CPU usage goes up

The most common cause?

👉 missing indexes.

Indexes are like a table of contents in a book — they help PostgreSQL jump straight to the data instead of scanning everything.

Here’s how you add them:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

It’s a tiny change that makes a massive difference — especially once you hit thousands or millions of records.

---

Problem #4 — How Many Times Did a User Buy the Same Product?

Sometimes you want to analyze repeat purchases or buying patterns. For example: which users keep ordering the same product?

Here’s one way to check:

SELECT 
  u.name,
  p.product_name,
  SUM(o.quantity) AS total_qty
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY u.name, p.product_name
ORDER BY total_qty DESC;

This is useful for:

✔ marketing
✔ inventory planning
✔ user behavior analysis

---

Problem #5 — What If I Update the Wrong Data?

Let’s say you accidentally run:

UPDATE products SET price = 0;

Suddenly everything in the store is free. Customers celebrate.
You panic.

That’s why transactions exist.

BEGIN;

UPDATE products SET price = 0;

-- review first

COMMIT;

-- or undo
ROLLBACK;

Think of BEGIN and COMMIT like a safety net.
Nothing becomes permanent until you confirm it.

A good habit is this:

👉 always run SELECT first
👉 then UPDATE once you're sure

---

Problem #6 — How Do We Prevent Duplicate Emails?

Email should always be unique. We already defined:

email VARCHAR(120) UNIQUE

But to be extra safe:

CREATE UNIQUE INDEX unique_email ON users(email);

That’s an extra layer of protection.

---

Problem #7 — How Do We Find Users Who Never Bought Anything?

This is a surprisingly common business question — especially for remarketing or email campaigns.

Here’s the query:

SELECT 
  u.id,
  u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Meaning:

• LEFT JOIN keeps all users

• users with no orders show NULL

• we filter those

Now you have a list of inactive users.

---

Best Practices to Build a Healthy PostgreSQL Database

Here are a few friendly reminders:

✔ Give tables meaningful names
✔ Use the right data type
✔ Create indexes for frequently searched fields
✔ Avoid deleting data without backups
✔ Prefer transactions for bulk changes
✔ Keep queries simple when possible