Databases Lesson 7: PostgreSQL Deep Dive

PostgreSQL is the most powerful open-source relational database. It has features that go far beyond basic SQL.

JSON in PostgreSQL

-- Store flexible JSON data
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  metadata JSONB  -- JSONB = indexed, queryable JSON!
);

INSERT INTO products (name, metadata) VALUES
  ('Laptop', '{"brand":"Dell","specs":{"ram":16,"cpu":"i7"},"tags":["featured","sale"]}');

-- Query JSON fields
SELECT name, metadata->'brand' as brand FROM products;
SELECT * FROM products WHERE metadata->>'brand' = 'Dell';
SELECT * FROM products WHERE metadata->'specs'->>'ram' = '16';
SELECT * FROM products WHERE metadata->'tags' ? 'sale';

Window Functions

-- Rank products by price within each category
SELECT
  name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) as rank,
  AVG(price) OVER (PARTITION BY category) as avg_category_price
FROM products;

-- Running total of sales
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

CTEs (Common Table Expressions)

-- Readable, reusable subqueries
WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', created_at) as month, SUM(total) as revenue
  FROM orders GROUP BY month
),
top_months AS (
  SELECT * FROM monthly_sales WHERE revenue > 10000
)
SELECT * FROM top_months ORDER BY revenue DESC;