Switching from MySQL to PostgreSQL

I've never used PostgreSQL from scratch in production before, it's a great system but even so if you're familiar with MySQL and starting a project with PostgreSQL, the transition can feel a bit unfamiliar at first.

Why would you use it then? Well, in my case it was specified before I joined the project, but actually even though MySQL is plenty sufficient most of the time, Postgres does have a lot of powerful features missing from MySQL, so it is worth at least being familiar with it.

PostgreSQL vs MySQL: Key Differences

Feature	PostgreSQL	MySQL
Philosophy	Standards-compliant, extensible, correctness-focused	Speed-focused, simpler setup
ACID Compliance	Fully ACID-compliant by default	ACID only with InnoDB engine
Data Types	Rich: arrays, JSONB, custom types, strict typing	Basic: looser typing, simpler types
JSON Support	Advanced querying with JSONB and indexes	Basic JSON support (since 5.7)
Index Types	B-tree, GIN, GiST, BRIN, partial indexes, expression indexes	Mostly B-tree, limited functional index support
Stored Procedures	PL/pgSQL, Python, Perl, and more	Simpler stored procedures
Extensions	Extensible (e.g. PostGIS, uuid-ossp)	Less flexible
Concurrency (MVCC)	Excellent MVCC, avoids table-level locks	MVCC via InnoDB, but still locking under load
CTEs / Window Functions	Fully supported	Available from MySQL 8.0+
Tooling	psql, pgAdmin, DBeaver, Postico	mysql, Workbench, phpMyAdmin
Licensing	Open source, permissive PostgreSQL license	GPL, owned by Oracle
Replication	Streaming + logical replication supported out of the box	Mature binlog-based replication

---

PostgreSQL vs MySQL SQL Syntax

Here are some differences you'll run into when writing SQL for PostgreSQL after using MySQL.

Auto-Increment / Identity

-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- or (preferred for standards)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

'UPSERT':

MySQL: ON DUPLICATE KEY UPDATE
PostgreSQL: ON CONFLICT (...) DO UPDATE

Booleans:

MySQL: TINYINT(1)
PostgreSQL: BOOLEAN (TRUE/FALSE)

Case sensitivity:

MySQL: often case-insensitive
PostgreSQL: case-sensitive, use ILIKE for insensitive search

Example:
SELECT * FROM users WHERE name ILIKE 'chris';

IF/CASE

-- MySQL
SELECT IF(score > 50, 'Pass', 'Fail') FROM exams;

-- PostgreSQL
SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM exams;

Dates

-- Current timestamp
SELECT NOW(); -- same in both

-- Extract year
-- MySQL
SELECT YEAR(created_at) FROM orders;

-- PostgreSQL
SELECT EXTRACT(YEAR FROM created_at) FROM orders;

Strings

Type	MySQL	PostgreSQL
String literals	'text'	'text'
Identifiers	`name`	"name" (strict)

String Concatenation:

MySQL: CONCAT(a, b)
PostgreSQL: a || b