MySQL vs PostgreSQL: Which Database Should You Choose in 2024?

-- ============================================
-- Run this in MySQL to create a users table
-- ============================================

-- MySQL syntax: note the use of AUTO_INCREMENT
-- and the TINYINT(1) shorthand for booleans
CREATE TABLE users (
    user_id       INT AUTO_INCREMENT PRIMARY KEY,  -- MySQL auto-increments the ID
    username      VARCHAR(50) NOT NULL UNIQUE,      -- must be unique, can't be null
    email         VARCHAR(100) NOT NULL UNIQUE,
    is_active     TINYINT(1) DEFAULT 1,             -- MySQL uses TINYINT for booleans
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert a sample user
INSERT INTO users (username, email)
VALUES ('alex_dev', 'alex@example.com');

-- Retrieve all active users
SELECT user_id, username, email, created_at
FROM   users
WHERE  is_active = 1;

-- ============================================
-- Now the SAME table in PostgreSQL
-- ============================================

-- PostgreSQL syntax: note SERIAL instead of AUTO_INCREMENT
-- and native BOOLEAN type instead of TINYINT
CREATE TABLE users (
    user_id       SERIAL PRIMARY KEY,              -- SERIAL = auto-incrementing integer
    username      VARCHAR(50) NOT NULL UNIQUE,
    email         VARCHAR(100) NOT NULL UNIQUE,
    is_active     BOOLEAN DEFAULT TRUE,            -- PostgreSQL has a real BOOLEAN type
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a sample user (identical SQL — this part doesn't change)
INSERT INTO users (username, email)
VALUES ('alex_dev', 'alex@example.com');

-- Retrieve all active users
SELECT user_id, username, email, created_at
FROM   users
WHERE  is_active = TRUE;  -- PostgreSQL understands TRUE/FALSE natively

-- ============================================
-- PostgreSQL-specific features not in MySQL
-- ============================================

-- 1. NATIVE ARRAY COLUMNS
-- Store multiple tags per article without a join table
CREATE TABLE articles (
    article_id   SERIAL PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    tags         TEXT[],                          -- TEXT[] means: an array of text values
    view_counts  INT[] DEFAULT '{}'
);

INSERT INTO articles (title, tags)
VALUES
    ('Getting Started with Postgres', ARRAY['database', 'postgresql', 'beginner']),
    ('MySQL Performance Tips',        ARRAY['database', 'mysql', 'performance']);

-- Find all articles tagged 'database'
-- @> means: left array CONTAINS right array
SELECT title, tags
FROM   articles
WHERE  tags @> ARRAY['database'];

-- 2. NATIVE JSONB COLUMN
-- Store flexible product metadata without forcing a rigid schema
CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        NUMERIC(10, 2) NOT NULL,
    metadata     JSONB                            -- JSONB = binary JSON, fast and queryable
);

INSERT INTO products (name, price, metadata)
VALUES
    ('Mechanical Keyboard', 129.99,
     '{"color": "black", "switches": "Cherry MX Blue", "wireless": false}'),
    ('Wireless Mouse', 49.99,
     '{"color": "white", "dpi": 1600, "wireless": true}');

-- Query inside the JSON using the ->> operator
-- ->> extracts a JSON field as TEXT
SELECT name, price,
       metadata->>'color'    AS color,           -- pulls 'color' key from JSON
       metadata->>'wireless' AS is_wireless
FROM   products
WHERE  (metadata->>'wireless')::BOOLEAN = TRUE;  -- cast the JSON string to a real BOOLEAN

-- 3. FULL-TEXT SEARCH (more capable than MySQL's version)
SELECT title
FROM   articles
WHERE  to_tsvector('english', title) @@ to_tsquery('english', 'Postgres');

-- ============================================
-- Transaction example — works in both MySQL (InnoDB) and PostgreSQL
-- This pattern is CRITICAL for financial or inventory operations
-- ============================================

-- Setup: an orders table and an inventory table
CREATE TABLE inventory (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    stock_count   INT NOT NULL CHECK (stock_count >= 0)  -- can't go negative
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,  -- use AUTO_INCREMENT in MySQL
    product_id    INT NOT NULL REFERENCES inventory(product_id),
    quantity      INT NOT NULL,
    order_status  VARCHAR(20) DEFAULT 'pending',
    ordered_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO inventory (product_id, product_name, stock_count)
VALUES (101, 'Mechanical Keyboard', 5);

-- ============================================
-- The SAFE way to place an order (using a transaction)
-- ============================================
BEGIN;  -- start the transaction — nothing is saved to disk yet

    -- Step 1: reduce stock
    UPDATE inventory
    SET    stock_count = stock_count - 1
    WHERE  product_id = 101;

    -- Step 2: create the order record
    INSERT INTO orders (product_id, quantity, order_status)
    VALUES (101, 1, 'confirmed');

    -- If BOTH steps succeed, make it permanent
COMMIT;

-- ============================================
-- What happens when something goes wrong
-- ============================================
BEGIN;

    UPDATE inventory
    SET    stock_count = stock_count - 10  -- trying to buy 10, but only 5 in stock
    WHERE  product_id = 101;

    -- PostgreSQL: the CHECK constraint (stock_count >= 0) fires HERE
    -- MySQL InnoDB: also fires the CHECK constraint (MySQL 8.0+)
    -- If this UPDATE fails, we roll back EVERYTHING in this transaction

    INSERT INTO orders (product_id, quantity, order_status)
    VALUES (101, 10, 'confirmed');  -- this line never runs if the UPDATE failed

ROLLBACK;  -- undo everything — stock is restored, no order created

-- Verify stock is unchanged after the failed transaction
SELECT product_name, stock_count
FROM   inventory
WHERE  product_id = 101;

-- ============================================
-- Window functions — available in both, but PostgreSQL
-- handles them more consistently and with more options
-- This example ranks employees by salary within each department
-- ============================================

CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    department    VARCHAR(50) NOT NULL,
    annual_salary NUMERIC(10, 2) NOT NULL
);

INSERT INTO employees (full_name, department, annual_salary)
VALUES
    ('Sarah Chen',     'Engineering', 95000),
    ('Marcus Webb',    'Engineering', 88000),
    ('Priya Nair',     'Engineering', 102000),
    ('Jordan Blake',   'Marketing',   72000),
    ('Taylor Simmons', 'Marketing',   68000),
    ('Sam Rivera',     'Marketing',   75000);

-- RANK() OVER PARTITION BY: rank each employee by salary
-- within their own department (not across the whole company)
SELECT
    full_name,
    department,
    annual_salary,
    -- PARTITION BY splits the ranking by department
    -- ORDER BY DESC means highest salary gets rank 1
    RANK() OVER (
        PARTITION BY department
        ORDER BY annual_salary DESC
    ) AS salary_rank_in_dept,

    -- Also show each person's salary vs department average
    ROUND(
        AVG(annual_salary) OVER (PARTITION BY department), 2
    ) AS dept_avg_salary,

    -- Running total of salary within department (ordered by salary)
    SUM(annual_salary) OVER (
        PARTITION BY department
        ORDER BY annual_salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_dept_salary

FROM employees
ORDER BY department, salary_rank_in_dept;