MySQL vs PostgreSQL: What Companies Actually Use Today

#!/usr/bin/env bash
# Start both databases locally for practice
docker run -d --name mysql-practice -e MYSQL_ROOT_PASSWORD=practice \
  -p 3306:3306 mysql:8

docker run -d --name pg-practice -e POSTGRES_PASSWORD=practice \
  -p 5432:5432 postgres:16

# Connect:
#   docker exec -it mysql-practice mysql -uroot -ppractice
#   docker exec -it pg-practice psql -U postgres

-- MySQL
SELECT VERSION();
SHOW DATABASES;

-- PostgreSQL
SELECT version();
\l          -- list databases (psql command)
\dt         -- list tables in current database (psql command)

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  city VARCHAR(100),
  spend DECIMAL(10, 2) DEFAULT 0
);

INSERT INTO customers (email, city, spend)
VALUES ('asha@example.com', 'Pune', 500)
ON DUPLICATE KEY UPDATE spend = spend + VALUES(spend);

SELECT CONCAT(city, ' - ', email) AS label
FROM customers
WHERE email LIKE '%@example.com'
LIMIT 10;

CREATE TABLE customers (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  city VARCHAR(100),
  spend NUMERIC(10, 2) DEFAULT 0
);

INSERT INTO customers (email, city, spend)
VALUES ('asha@example.com', 'Pune', 500)
ON CONFLICT (email) DO UPDATE
  SET spend = customers.spend + EXCLUDED.spend;

SELECT city || ' - ' || email AS label
FROM customers
WHERE email ILIKE '%@EXAMPLE.com'
LIMIT 10;

-- Works in MySQL 8+ and PostgreSQL
WITH monthly AS (
  SELECT city,
         EXTRACT(YEAR FROM order_date)  AS yr,
         EXTRACT(MONTH FROM order_date) AS mon,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY city, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
)
SELECT city, yr, mon, revenue,
       RANK() OVER (PARTITION BY yr, mon ORDER BY revenue DESC) AS city_rank,
       SUM(revenue) OVER (PARTITION BY city ORDER BY yr, mon) AS running_total
FROM monthly
ORDER BY yr, mon, city_rank;

-- MySQL: extract a field from a JSON column
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(details, '$.payment_method')) AS method
FROM orders
WHERE JSON_EXTRACT(details, '$.coupon_used') = true;

-- PostgreSQL (JSONB): operators and an index
SELECT id, details ->> 'payment_method' AS method
FROM orders
WHERE (details ->> 'coupon_used')::boolean = true;

CREATE INDEX idx_orders_details ON orders USING GIN (details);

If your target is...                Start with
Web development (PHP, CMS stacks)     MySQL
Data analyst / analytics engineer     PostgreSQL
Backend with modern frameworks        PostgreSQL
Not sure                              Either; focus on standard SQL

After the first, spend one week on the other's differences.

Week 1  SELECT, WHERE, ORDER BY, LIMIT
Week 2  JOINs (inner, left), GROUP BY, HAVING
Week 3  Subqueries and CTEs
Week 4  Window functions (RANK, LAG, running totals)
Week 5  Table design, keys, indexes, constraints
Week 6  Dialect differences: IDs, upserts, JSON, string functions

Project: Sales database in both MySQL and PostgreSQL

1. Create the same tables in both (note syntax changes)
2. Load the same CSV data
3. Run 10 analysis queries; note which needed changes
4. Write a short README: 'What changed between dialects'

Shows SQL depth and adaptability.