MySQL and PostgreSQL are the two dominant open-source relational databases. PostgreSQL is more standards-compliant and feature-rich. MySQL/MariaDB is simpler and powers most LAMP stacks. Both run on Ubuntu and deploy on every cloud platform.
| Statement / Feature | Syntax | Description |
|---|---|---|
| MySQL connect | mysql -u root -p / mysql -h host -P 3306 -u user -p dbname | MySQL CLI connection |
| PostgreSQL connect | psql -U postgres / psql postgresql://user:pass@host/db | PostgreSQL CLI connection |
| MySQL databases | SHOW DATABASES; USE dbname; SHOW TABLES; DESCRIBE table; | MySQL navigation |
| PostgreSQL databases | \l / \c dbname / \dt / \d tablename / \du | psql meta-commands |
| MySQL create DB | CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | MySQL database creation |
| PostgreSQL create DB | CREATE DATABASE mydb OWNER myuser ENCODING 'UTF8'; | PostgreSQL database creation |
| AUTO_INCREMENT | id INT AUTO_INCREMENT PRIMARY KEY | MySQL auto-increment |
| SERIAL / IDENTITY | id SERIAL PRIMARY KEY / GENERATED ALWAYS AS IDENTITY | PostgreSQL auto-increment |
| MySQL string funcs | CONCAT, SUBSTRING, LOWER, UPPER, TRIM, LENGTH, REPLACE | MySQL string functions |
| PostgreSQL string | ||, SUBSTRING, lower, upper, trim, length, replace, regexp_replace | PostgreSQL string operators/functions |
| MySQL datetime | NOW(), CURDATE(), DATE_FORMAT(d,'%Y-%m'), DATEDIFF, DATE_ADD | MySQL date functions |
| PostgreSQL datetime | NOW(), CURRENT_DATE, TO_CHAR(d,'YYYY-MM'), AGE(), INTERVAL '1 day' | PostgreSQL date functions |
| MySQL JSON | JSON_EXTRACT(col,'$.key'), JSON_SET, JSON_ARRAYAGG | MySQL JSON functions (5.7+) |
| PostgreSQL JSON | col->'key', col->>'key', jsonb_set, jsonb_agg, @>, ? | PostgreSQL JSONB operators |
| PostgreSQL JSONB index | CREATE INDEX idx ON table USING GIN(jsonb_col) | Index JSONB for fast queries |
| UPSERT MySQL | INSERT INTO t (...) VALUES (...) ON DUPLICATE KEY UPDATE col=val | MySQL upsert |
| UPSERT PostgreSQL | INSERT INTO t (...) VALUES (...) ON CONFLICT (col) DO UPDATE SET col=EXCLUDED.col | PostgreSQL upsert |
| EXPLAIN | EXPLAIN SELECT ... / EXPLAIN ANALYZE SELECT ... | Show query execution plan |
| ANALYZE | ANALYZE table / VACUUM ANALYZE table (PG) | Update statistics for query planner |
| TRANSACTION | BEGIN; ... COMMIT; / ROLLBACK; | Group statements atomically |
| SAVEPOINT | SAVEPOINT sp1; ... ROLLBACK TO sp1; | Partial rollback within transaction |
| ISOLATION LEVELS | SET TRANSACTION ISOLATION LEVEL READ COMMITTED / REPEATABLE READ / SERIALIZABLE | Control concurrent access |
| MySQL engine | ENGINE=InnoDB (default, transactions) vs ENGINE=MyISAM (no transactions) | MySQL storage engine |
| PostgreSQL schemas | CREATE SCHEMA myschema; SET search_path TO myschema; | Namespace within database |
| MySQL FULLTEXT | CREATE FULLTEXT INDEX idx ON t(col); MATCH(col) AGAINST('search') | MySQL full-text search |
| PostgreSQL FTS | to_tsvector('english',col) @@ to_tsquery('english','search') | PostgreSQL full-text search |
| PostgreSQL arrays | ARRAY[1,2,3] / col = ANY(ARRAY[1,2,3]) / col @> ARRAY[1] | PostgreSQL native arrays |
-- MySQL vs PostgreSQL — key differences and features
-- ── MySQL ─────────────────────────────────────────────────────
-- Connect: mysql -u root -p
-- Create database
CREATE DATABASE mywebuniversity
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE mywebuniversity;
-- Table with MySQL-specific features
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
score DECIMAL(5,2),
bio TEXT,
metadata JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
FULLTEXT INDEX idx_bio (bio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- MySQL JSON operations
INSERT INTO students (name,email,score,metadata) VALUES
('Alice','alice@mwu.com',95.0,'{"level":"senior","tags":["python","go"]}'),
('Bob', 'bob@mwu.com', 87.0,'{"level":"mid", "tags":["java","rust"]}');
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.level')) AS level,
JSON_EXTRACT(metadata,'$.tags[0]') AS primary_lang
FROM students;
-- MySQL upsert
INSERT INTO students (name,email,score) VALUES ('Alice','alice@mwu.com',98.0)
ON DUPLICATE KEY UPDATE score=VALUES(score), updated_at=NOW();
-- FULLTEXT search
SELECT name, MATCH(bio) AGAINST('python machine learning') AS relevance
FROM students WHERE MATCH(bio) AGAINST('python machine learning') > 0
ORDER BY relevance DESC;
-- ── PostgreSQL ────────────────────────────────────────────────
-- Connect: psql -U postgres
-- CREATE DATABASE mywebuniversity OWNER postgres;
-- \c mywebuniversity
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
score DECIMAL(5,2),
bio TEXT,
tags TEXT[], -- native array!
metadata JSONB, -- binary JSON
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- PostgreSQL indexes
CREATE INDEX idx_tags ON students USING GIN(tags);
CREATE INDEX idx_meta ON students USING GIN(metadata);
CREATE INDEX idx_bio_fts ON students USING GIN(to_tsvector('english', COALESCE(bio,'')));
-- PostgreSQL JSONB
INSERT INTO students (name,email,score,tags,metadata) VALUES
('Alice','alice@mwu.com',95,'{"python","go"}','{"level":"senior","yoe":5}'),
('Bob', 'bob@mwu.com', 87,'{"java","rust"}', '{"level":"mid", "yoe":2}');
SELECT name, metadata->>'level' AS level, metadata->'yoe' AS years
FROM students WHERE metadata @> '{"level":"senior"}';
-- Array operations
SELECT name FROM students WHERE 'python' = ANY(tags);
SELECT name, array_length(tags,1) AS tag_count FROM students;
-- PostgreSQL upsert
INSERT INTO students (name,email,score) VALUES ('Alice','alice@mwu.com',99.0)
ON CONFLICT (email) DO UPDATE SET score=EXCLUDED.score, created_at=NOW();
-- PostgreSQL full-text search
SELECT name, ts_rank(to_tsvector('english',bio), query) AS rank
FROM students, to_tsquery('english','python & learning') query
WHERE to_tsvector('english',bio) @@ query
ORDER BY rank DESC;← Aggregate Functions | 🏠 Index | Transactions & Performance →