PostgreSQL for Developers

PostgreSQL is incredibly powerful. Let's explore features that go beyond basic CRUD operations.

1. JSON/JSONB Support

PostgreSQL handles JSON natively, making it perfect for flexible schemas:

-- Create a table with JSONB
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  attributes JSONB
);

-- Insert with JSON
INSERT INTO products (name, attributes) VALUES 
  ('Laptop', '{"brand": "Dell", "specs": {"ram": "16GB", "ssd": "512GB"}}');

-- Query nested JSON
SELECT * FROM products 
WHERE attributes->'specs'->>'ram' = '16GB';

-- Index JSONB for performance
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

2. Window Functions

Perfect for analytics and reporting:

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

-- Rank within groups
SELECT 
  department,
  employee,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

3. Common Table Expressions (CTEs)

Write readable, maintainable queries:

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) as total
  FROM orders
  GROUP BY 1
),
growth AS (
  SELECT 
    month,
    total,
    LAG(total) OVER (ORDER BY month) as prev_month,
    (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month) * 100 as growth_pct
  FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct > 10;

4. Full-Text Search

Built-in search without external tools:

-- Add search vector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Populate and index
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- Search
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', 'react & typescript');

Conclusion

PostgreSQL offers incredible features out of the box. Master these, and you'll build better applications.