PostgreSQL vs MySQL: Differences and Which to Learn

PostgreSQL and MySQL are the two most popular open-source databases, and for most projects either one works. Both are relational, both speak SQL, both are free and both run everything from student projects to huge companies.

The differences that matter are smaller and sneakier than "which is faster": a comparison that ignores capital letters, a division that throws away decimals, a table you can't roll back. This guide covers those, plus features, licensing and hosting, and ends with a simple rule for choosing.

The short answer

• Choose PostgreSQL for a new project when you're free to pick. It's stricter, has more features (JSON indexing, arrays, extensions like PostGIS and pgvector) and it's what most modern hosting platforms offer.
• Choose MySQL (or MariaDB) when the project already uses it, when you work with WordPress or other PHP software built for it, or when your hosting only offers MySQL.
• Learning SQL? Start with either. About 90% of what you learn transfers directly; this guide covers most of the other 10%.

Side by side

The SQL that behaves differently

These are the differences you'll actually hit when you move a project, or your habits, from one to the other.

Comparing text: capital letters

SELECT 'Nil' = 'nil';
-- PostgreSQL: false
-- MySQL:      1 (true)

MySQL's default collation (utf8mb4_0900_ai_ci) ignores case and accents, so = and LIKE match Nil, nil and NIL. PostgreSQL compares exactly. To ignore case there, use ILIKE, or compare lower(email) = lower($1).

This matters most for logins: on PostgreSQL, store emails in lowercase or two accounts can exist for the same person.

Dividing integers

SELECT 7 / 2;
-- PostgreSQL: 3
-- MySQL:      3.5000

PostgreSQL keeps the type: integer divided by integer is an integer, and the decimals are dropped. Write 7 / 2.0 or cast one side (7::numeric / 2) to get 3.5. MySQL does the opposite; its DIV operator is the one that drops decimals (7 DIV 2 is 3). Averages and percentages calculated by hand are where this bites.

Joining strings with ||

SELECT 'a' || 'b';
-- PostgreSQL: ab
-- MySQL:      0, with a warning

In MySQL, || means logical OR by default (and that use is deprecated), so the strings are converted to numbers. Use CONCAT('a', 'b'), which works in both.

Auto-increment IDs and getting them back

-- PostgreSQL
CREATE TABLE users (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text UNIQUE NOT NULL,
  name  text
);
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana') RETURNING id;

-- MySQL
CREATE TABLE users (
  id    int AUTO_INCREMENT PRIMARY KEY,
  email varchar(255) UNIQUE NOT NULL,
  name  varchar(100)
);
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana');
SELECT LAST_INSERT_ID();

PostgreSQL's RETURNING gives you the new row's columns in the same statement, which also works on UPDATE and DELETE. MySQL doesn't support RETURNING (MariaDB does, for INSERT and DELETE). You'll still see SERIAL in older PostgreSQL tutorials; identity columns are the standard way now.

Insert or update (upsert)

-- PostgreSQL
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana G.')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- MySQL 8.0.19 and later
INSERT INTO users (email, name) VALUES ('ana@example.com', 'Ana G.') AS new
ON DUPLICATE KEY UPDATE name = new.name;

Same idea, different syntax. In MySQL, the older VALUES(name) form still works but is deprecated.

Rolling back a schema change

BEGIN;
CREATE TABLE tmp_x (id int);
ROLLBACK;
-- PostgreSQL: tmp_x doesn't exist
-- MySQL:      tmp_x still exists

In PostgreSQL almost every schema change can run inside a transaction, so a migration that fails halfway leaves the database as it was. In MySQL, statements like CREATE, ALTER and DROP commit immediately, so a broken migration can leave you with half the changes applied.

Table names and capital letters

PostgreSQL turns unquoted names into lowercase: CREATE TABLE Orders creates orders. If you create it with quotes, as "Orders", you have to quote it forever, and SELECT * FROM Orders fails with relation "orders" does not exist. The fix is simple: use lowercase names with underscores, and never quote them.

MySQL has a different trap. Table names are files on disk, so they're case-sensitive on Linux but not on Windows or macOS. A query with FROM Orders against a table called orders works on your Windows laptop and fails on the Linux server. Again: lowercase everywhere.

Quoting is different too: PostgreSQL uses "double quotes" for names and MySQL uses `backticks`. Strings use 'single quotes' in both.

Emoji and utf8

In MySQL, the character set called utf8 is really utf8mb3, which can't store emoji or some other characters:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 's'

New databases use utf8mb4 by default since MySQL 8.0, so this mostly appears in old tables and old tutorials that set CHARSET=utf8. Always use utf8mb4. PostgreSQL's UTF8 encoding handles every character.

Features

PostgreSQL's biggest advantage is how much it can do without another service:

• Types: native boolean, uuid, arrays, ranges, inet for IP addresses, and JSONB, a binary JSON type you can index and query with operators like ->, ->> and @>.
• Extensions: PostGIS for maps and geography, pgvector to store AI embeddings for RAG, pg_trgm for fuzzy text search, and many more.
• Security: fine-grained roles and row-level security, so the database itself can restrict which rows each user sees.

MySQL covers the common needs well: JSON columns with functions such as JSON_EXTRACT (and ->> on columns), full-text search, window functions and common table expressions since 8.0, and solid replication. It has fewer advanced types; BOOLEAN, for example, is stored as TINYINT(1), so SELECT true returns 1.

Strictness: less different than it used to be

Older MySQL was famous for silently accepting bad data: a string in a number column became 0, a date like 2026-02-30 was stored as zeros. Since MySQL 5.7, strict mode is on by default (STRICT_TRANS_TABLES), and those inserts fail with an error, as they do in PostgreSQL. If a tutorial says MySQL accepts anything, it's probably out of date, or the server has strict mode turned off.

Performance

For a typical web app, both are fast enough, and the bottleneck is your queries and indexes, not the engine. Benchmarks that crown a winner usually measure one workload with one configuration. MySQL has a reputation for simple, read-heavy queries; PostgreSQL tends to do better with complex queries, many joins and heavy concurrent writes. Add the right indexes and use EXPLAIN on slow queries in both.

Licensing and versions

• PostgreSQL uses the PostgreSQL License, a permissive license similar to MIT. It's developed by an independent community; a new major version comes out every year, and each one is supported for five years. PostgreSQL 18 came out in September 2025.
• MySQL is owned by Oracle. The Community edition is free under the GPLv2, and Oracle sells commercial licenses and an Enterprise edition. MariaDB is a fork started by MySQL's original creators; it's mostly compatible and is what many Linux distributions install when you ask for MySQL. MySQL 8.0 reached its end of life in April 2026; if you still use it, upgrade to 8.4 LTS or later.

For using either one in your own app, even a commercial one, the license makes no difference. It matters if you ship the database itself inside a product you sell.

Hosting

• MySQL is everywhere in traditional and shared hosting: cPanel hosts, WordPress and most PHP applications expect it.
• PostgreSQL is the default on most modern platforms and managed database services, including the serverless ones aimed at side projects.
• Your own server: both run in Docker in a few lines; see Docker Compose for beginners and backing up Docker volumes so you don't lose the data.

Try both locally in a minute:

docker run --rm -d --name pg -e POSTGRES_PASSWORD=change-me -p 5432:5432 postgres:18
docker run --rm -d --name my -e MYSQL_ROOT_PASSWORD=change-me -p 3306:3306 mysql:8.4

docker exec -it pg psql -U postgres
docker exec -it my mysql -uroot -p

Decision checklist

1. Does the project, the framework or the hosting already use one? → use that one
2. WordPress, or PHP software built for MySQL? → MySQL or MariaDB
3. Do you need JSON queries with indexes, geography, vector search or row-level security? → PostgreSQL
4. Starting a new project and free to choose? → PostgreSQL
5. Is it a small app on a single server with modest traffic? → also consider SQLite, which needs no server at all

FAQ

Should I learn PostgreSQL or MySQL first?

Either. The core of SQL (SELECT, JOIN, GROUP BY, indexes, transactions) is the same in both, so what you learn transfers. If your course or job uses one, start there; if not, PostgreSQL's stricter behavior teaches good habits.

Is PostgreSQL harder than MySQL?

Not really. Installing and using either is similar. PostgreSQL feels stricter at first because it rejects things MySQL lets through, like case-insensitive matches, but that strictness prevents bugs later.

Can I switch from MySQL to PostgreSQL later?

Yes. Tools like pgloader copy the schema and data, and an ORM hides most syntax differences in your code. The work is in the raw SQL: the differences in this guide, like upserts, ||, integer division and case-insensitive comparisons, are what you'll need to fix.

Is MariaDB the same as MySQL?

It started as a copy of MySQL in 2009 and is still mostly compatible: the same client commands and most of the same SQL. The two have drifted apart since, so check the documentation of the one you actually run, especially for JSON and newer features.

Which is more popular?

PostgreSQL has been the most used database among developers in the Stack Overflow Developer Survey for the last few years, with MySQL close behind. Both have huge communities, so you'll find answers for either.

Further reading

• PostgreSQL documentation
• MySQL 8.4 reference manual
• PostgreSQL 18 release announcement
• Related: SQLite vs PostgreSQL: which should you use?
• Related: FastAPI vs Flask vs Django
• Related: Docker Compose for beginners