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

---

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

String Concatenation:

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