PostgreSQL Tutorial: A Beginner's Guide to Database Essentials

CREATE OR REPLACE FUNCTION calculate_tax(amount numeric, rate numeric) 
RETURNS numeric AS $$
BEGIN
    RETURN amount * rate / 100;
END;
$$ LANGUAGE plpgsql;

# Ubuntu/Debian
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
 
# MacOS (using Homebrew)
brew install postgresql
brew services start postgresql

-- After installing Chat2DB, simply:
-- 1. Click "New Connection"
-- 2. Select PostgreSQL
-- 3. Enter your credentials
-- 4. Use natural language to generate queries

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary NUMERIC(10,2) CHECK (salary > 0),
    department_id INTEGER REFERENCES departments(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT e.name, d.department_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.lead_id
WHERE d.location = 'New York'
ORDER BY e.name;

SELECT 
    product_id,
    category,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank
FROM products;

SELECT 
    order_id,
    order_data->>'customer' as customer_name,
    jsonb_array_elements(order_data->'items')->>'product' as product
FROM orders
WHERE order_data @> '{"status": "completed"}';

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT r.region, p.product, SUM(o.quantity) as product_units
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN regional_sales r ON o.region = r.region
WHERE r.region IN (SELECT region FROM top_regions)
GROUP BY r.region, p.product;

-- Partitioning for large tables
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);
 
-- Create monthly partitions
CREATE TABLE sales_2023_01 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
 
-- Materialized views for performance
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    date_trunc('month', sale_date) as month,
    product_id,
    SUM(amount) as total_sales
FROM sales
GROUP BY 1, 2
WITH DATA;
 
-- Refresh the materialized view
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- Create an index for frequently queried columns
CREATE INDEX idx_employee_department ON employees(department_id);
 
-- Partial index for specific queries
CREATE INDEX idx_active_high_salary ON employees(salary)
WHERE active = true AND salary > 100000;
 
-- GIN index for JSONB data
CREATE INDEX idx_order_status ON orders USING GIN (order_data jsonb_path_ops);

EXPLAIN ANALYZE 
SELECT * FROM customers 
WHERE last_name LIKE 'Smith%' 
AND registration_date > '2023-01-01';

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE
);
 
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES categories(category_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(10,2) NOT NULL,
    shipping_address JSONB NOT NULL
);
 
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL,
    discount NUMERIC(10,2) DEFAULT 0
);

-- Create a searchable column
ALTER TABLE products ADD COLUMN search_vector tsvector;
 
-- Update the search vector
UPDATE products SET search_vector = 
    to_tsvector('english', name || ' ' || description);
 
-- Create a trigger to keep it updated
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON products FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(search_vector, 'pg_catalog.english', name, description);
 
-- Create a GIN index for fast searching
CREATE INDEX idx_product_search ON products USING GIN(search_vector);
 
-- Example search query
SELECT product_id, name, price
FROM products
WHERE search_vector @@ to_tsquery('english', 'organic & coffee')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'organic & coffee')) DESC;

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database()));
 
-- Identify slow queries
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
 
-- Monitor index usage
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;