PostgreSQL vs MySQL vs SQLite: differences

PostgreSQL, MySQL and SQLite all speak SQL, but not the same SQL. A statement that works on one of them meets one of three fates on another: it is refused with an error, it runs and returns a different answer, or it works as before. Refusals are the easy kind, because you notice them at once. The silent kind is the one that hurts: 7 / 2 is 3 in PostgreSQL and SQLite and 3.5000 in MySQL, and 'Asha' = 'asha' is false in two engines and true in the third.

This lesson runs the same statements, character for character, on all three engines and shows what each one printed. SQLite runs on this page: its outputs come from SQLite 3.49.1 through sql.js 1.14.2, and Run repeats them in your browser. The PostgreSQL 18.6 and MySQL 8.4.11 outputs were recorded on fresh, private servers that keep their default SQL settings, by the short Python programs shown beside them; the two helper files near the end say exactly how. At the end, one table holds every difference, with links to the lessons that explain them.

Three things can happen to a moved statement

A refusal tells you which line to fix, and the fix is usually a different spelling of the same idea. A different answer tells you nothing, so the only defence is a test that runs on the new engine with real data and compares the results. Keep that split in mind as you read the outputs: every section below has both kinds.

Numbers, text and comparisons

-- Run unchanged on SQLite, PostgreSQL and MySQL: numbers, text, true and false, and comparisons.
SELECT 7 / 2 AS int_div, 7.0 / 2 AS dec_div, 7 % 3 AS remainder;
SELECT 'Ra' || 'vi' AS pipes;
SELECT concat('Ra', NULL, 'vi') AS concat_null, 'Ra' || NULL AS pipes_null;
SELECT true AS yes, 1 = 1 AS equal;
SELECT true + 1 AS true_plus_one;
SELECT upper('école') AS upper_e, 'Asha' = 'asha' AS same_text, 'Asha' LIKE 'asha' AS like_match;

┌─────────┬─────────┬───────────┐
│ int_div │ dec_div │ remainder │
├─────────┼─────────┼───────────┤
│ 3       │ 3.5     │ 1         │
└─────────┴─────────┴───────────┘
┌───────┐
│ pipes │
├───────┤
│ Ravi  │
└───────┘
┌─────────────┬────────────┐
│ concat_null │ pipes_null │
├─────────────┼────────────┤
│ Ravi        │            │
└─────────────┴────────────┘
┌─────┬───────┐
│ yes │ equal │
├─────┼───────┤
│ 1   │ 1     │
└─────┴───────┘
┌───────────────┐
│ true_plus_one │
├───────────────┤
│ 2             │
└───────────────┘
┌─────────┬───────────┬────────────┐
│ upper_e │ same_text │ like_match │
├─────────┼───────────┼────────────┤
│ éCOLE   │ 0         │ 1          │
└─────────┴───────────┴────────────┘

"""The statements of ../same-sql/values.sql on PostgreSQL 18.6, as psql prints them."""

from pg_server import psql_file

psql_file("../same-sql/values.sql")

 int_div |      dec_div       | remainder 
---------+--------------------+-----------
       3 | 3.5000000000000000 |         1
(1 row)

 pipes 
-------
 Ravi
(1 row)

 concat_null | pipes_null 
-------------+------------
 Ravi        | NULL
(1 row)

 yes | equal 
-----+-------
 t   | t
(1 row)

ERROR:  operator does not exist: boolean + integer
LINE 1: SELECT true + 1 AS true_plus_one;
                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
 upper_e | same_text | like_match 
---------+-----------+------------
 ÉCOLE   | f         | f
(1 row)

"""The statements of ../same-sql/values.sql on MySQL 8.4.11, as the mysql client prints them."""

from mysql_server import mysql_file

mysql_file("../same-sql/values.sql")

+---------+---------+-----------+
| int_div | dec_div | remainder |
+---------+---------+-----------+
|  3.5000 | 3.50000 |         1 |
+---------+---------+-----------+
+-------+
| pipes |
+-------+
|     0 |
+-------+
Warning (Code 1287): '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
Warning (Code 1292): Truncated incorrect DOUBLE value: 'Ra'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'vi'
+-------------+------------+
| concat_null | pipes_null |
+-------------+------------+
| NULL        |       NULL |
+-------------+------------+
Warning (Code 1287): '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
Warning (Code 1292): Truncated incorrect DOUBLE value: 'Ra'
+-----+-------+
| yes | equal |
+-----+-------+
|   1 |     1 |
+-----+-------+
+---------------+
| true_plus_one |
+---------------+
|             2 |
+---------------+
+---------+-----------+------------+
| upper_e | same_text | like_match |
+---------+-----------+------------+
| ÉCOLE   |         1 |          1 |
+---------+-----------+------------+

• Division. PostgreSQL and SQLite divide one whole number by another to a whole number, so 7 / 2 is 3. MySQL’s / always keeps a fraction, with four more decimal places than its first operand has, so it prints 3.5000. Write 7 * 1.0 / 2 or CAST(7 AS DECIMAL(10, 2)) / 2 when you want the fraction everywhere.
• || and concat(). In PostgreSQL and SQLite, || joins two strings. In MySQL it means OR unless the SQL mode says otherwise, so 'Ra' || 'vi' turned both strings into the number 0 and printed 0, with three warnings, one of them saying that || as OR is deprecated. concat() exists in all three but treats NULL differently: PostgreSQL and SQLite skip a NULL argument and return Ravi, MySQL returns NULL for the whole result.
• True and false. PostgreSQL has a real boolean type and shows t and f. MySQL and SQLite store true as 1 and false as 0, which is why true + 1 is 2 there and an error in PostgreSQL.
• Letter case. SQLite’s upper() and lower() change only the letters A to Z, so upper('école') kept its é. PostgreSQL follows the locale of the database, here its builtin C.UTF-8, and MySQL its collation; both returned ÉCOLE.
• Comparing text. 'Asha' = 'asha' is false in PostgreSQL and SQLite and true in MySQL, whose default collation, utf8mb4_0900_ai_ci, ignores case and accents. LIKE adds a third answer: PostgreSQL’s LIKE counts capitals, SQLite’s ignores the case of A to Z, and MySQL’s follows the collation.

Three engines, three different answers to two lines about one name: that is why letter case belongs in every migration test.

Sorting, grouping and row limits

-- Run unchanged on SQLite, PostgreSQL and MySQL: NULLs in ORDER BY, GROUP BY rules and row limits.
CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  city     VARCHAR(20) NOT NULL,
  customer VARCHAR(20) NOT NULL,
  amount   INTEGER
);
INSERT INTO orders VALUES
  (1, 'Pune', 'Asha', 400), (2, 'Pune', 'Ravi', 900), (3, 'Kochi', 'Meena', NULL),
  (4, 'Kochi', 'Arjun', 250), (5, 'Jaipur', 'Tara', 600);

-- 1. Where does the NULL amount go? Then the same order on every engine.
SELECT id, amount FROM orders ORDER BY amount;
SELECT id, amount FROM orders ORDER BY amount IS NULL, amount;

-- 2. A column that is neither grouped nor aggregated (a "bare" column).
SELECT city, customer, max(amount) AS top FROM orders GROUP BY city ORDER BY city;

-- 3. An alias from SELECT used in HAVING.
SELECT city, sum(amount) AS total FROM orders GROUP BY city HAVING total > 600 ORDER BY city;

-- 4. Three ways to ask for the 2nd and 3rd rows.
SELECT id FROM orders ORDER BY id LIMIT 2 OFFSET 1;
SELECT id FROM orders ORDER BY id OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY;
SELECT id FROM orders ORDER BY id LIMIT 1, 2;

┌────┬────────┐
│ id │ amount │
├────┼────────┤
│ 3  │        │
│ 4  │ 250    │
│ 1  │ 400    │
│ 5  │ 600    │
│ 2  │ 900    │
└────┴────────┘
┌────┬────────┐
│ id │ amount │
├────┼────────┤
│ 4  │ 250    │
│ 1  │ 400    │
│ 5  │ 600    │
│ 2  │ 900    │
│ 3  │        │
└────┴────────┘
┌────────┬──────────┬─────┐
│  city  │ customer │ top │
├────────┼──────────┼─────┤
│ Jaipur │ Tara     │ 600 │
│ Kochi  │ Arjun    │ 250 │
│ Pune   │ Ravi     │ 900 │
└────────┴──────────┴─────┘
┌──────┬───────┐
│ city │ total │
├──────┼───────┤
│ Pune │ 1300  │
└──────┴───────┘
┌────┐
│ id │
├────┤
│ 2  │
│ 3  │
└────┘
┌────┐
│ id │
├────┤
│ 2  │
│ 3  │
└────┘

Parse error near line 24: near "OFFSET": syntax error

"""The statements of ../same-sql/grouping.sql on PostgreSQL 18.6, as psql prints them."""

from pg_server import psql_file

psql_file("../same-sql/grouping.sql")

 id | amount 
----+--------
  4 |    250
  1 |    400
  5 |    600
  2 |    900
  3 |   NULL
(5 rows)

 id | amount 
----+--------
  4 |    250
  1 |    400
  5 |    600
  2 |    900
  3 |   NULL
(5 rows)

ERROR:  column "orders.customer" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT city, customer, max(amount) AS top FROM orders GROUP ...
                     ^
ERROR:  column "total" does not exist
LINE 1: ...amount) AS total FROM orders GROUP BY city HAVING total > 60...
                                                             ^
 id 
----
  2
  3
(2 rows)

 id 
----
  2
  3
(2 rows)

ERROR:  LIMIT #,# syntax is not supported
LINE 1: SELECT id FROM orders ORDER BY id LIMIT 1, 2;
                                          ^
HINT:  Use separate LIMIT and OFFSET clauses.

"""The statements of ../same-sql/grouping.sql on MySQL 8.4.11, as the mysql client prints them."""

from mysql_server import mysql_file

mysql_file("../same-sql/grouping.sql")

+----+--------+
| id | amount |
+----+--------+
|  3 |   NULL |
|  4 |    250 |
|  1 |    400 |
|  5 |    600 |
|  2 |    900 |
+----+--------+
+----+--------+
| id | amount |
+----+--------+
|  4 |    250 |
|  1 |    400 |
|  5 |    600 |
|  2 |    900 |
|  3 |   NULL |
+----+--------+
ERROR 1055 (42000) at line 17: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.customer' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+------+-------+
| city | total |
+------+-------+
| Pune |  1300 |
+------+-------+
+----+
| id |
+----+
|  2 |
|  3 |
+----+
ERROR 1064 (42000) at line 24: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY' at line 1
+----+
| id |
+----+
|  2 |
|  3 |
+----+

• Where NULLs sort. In an ascending sort, SQLite and MySQL put the NULL amount first and PostgreSQL puts it last. PostgreSQL and SQLite accept NULLS FIRST and NULLS LAST, MySQL does not. ORDER BY amount IS NULL, amount sorts the same way everywhere, because IS NULL is false, or 0, for every amount that exists.
• Bare columns. Only SQLite accepted customer in a grouped query without grouping or aggregating it. It took the customer from the row that holds the maximum, Ravi for Pune, and for Kochi Arjun, because the NULL amount is not a maximum. PostgreSQL refused the query, and MySQL refused it with error 1055 because ONLY_FULL_GROUP_BY is part of its default SQL mode.
• Aliases in HAVING. SQLite and MySQL let HAVING total > 600 use the alias from the select list. PostgreSQL answers column "total" does not exist, because HAVING is evaluated before the select list gets its names. Repeating the expression, HAVING sum(amount) > 600, works in all three.
• Row limits. LIMIT 2 OFFSET 1 works in all three. OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY is the spelling of the SQL standard, and of these three only PostgreSQL accepts it. MySQL’s own LIMIT 1, 2, offset first, also runs in SQLite, and PostgreSQL rejects it with a hint to use LIMIT and OFFSET.

Joins, set operations and window frames

-- Run unchanged on SQLite, PostgreSQL and MySQL: FULL JOIN, INTERSECT ALL and EXCEPT ALL, a GROUPS frame.
CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR(20) NOT NULL);
CREATE TABLE rooms (student_id INTEGER, room VARCHAR(10) NOT NULL);
INSERT INTO students VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena');
INSERT INTO rooms VALUES (1, 'A-101'), (2, 'B-204'), (9, 'C-310');

-- 1. Every student and every room, matched where possible.
SELECT name, room FROM students FULL JOIN rooms ON student_id = id ORDER BY name, room;

-- 2. Cities in both sign-up lists, and in the first only, counting repeats.
CREATE TABLE app_users (city VARCHAR(20));
CREATE TABLE web_users (city VARCHAR(20));
INSERT INTO app_users VALUES ('Pune'), ('Pune'), ('Kochi'), ('Surat');
INSERT INTO web_users VALUES ('Pune'), ('Pune'), ('Pune'), ('Kochi');
SELECT city FROM app_users INTERSECT ALL SELECT city FROM web_users ORDER BY city;
SELECT city FROM app_users EXCEPT ALL SELECT city FROM web_users ORDER BY city;

-- 3. A window frame counted in groups of equal values instead of rows.
SELECT id, sum(id) OVER (ORDER BY id GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS two_groups
FROM students ORDER BY id;

┌───────┬───────┐
│ name  │ room  │
├───────┼───────┤
│       │ C-310 │
│ Asha  │ A-101 │
│ Meena │       │
│ Ravi  │ B-204 │
└───────┴───────┘
┌────┬────────────┐
│ id │ two_groups │
├────┼────────────┤
│ 1  │ 1          │
│ 2  │ 3          │
│ 3  │ 5          │
└────┴────────────┘

Parse error near line 15: near "ALL": syntax error
Parse error near line 16: near "ALL": syntax error

"""The statements of ../same-sql/joins_sets.sql on PostgreSQL 18.6, as psql prints them."""

from pg_server import psql_file

psql_file("../same-sql/joins_sets.sql")

 name  | room  
-------+-------
 Asha  | A-101
 Meena | NULL
 Ravi  | B-204
 NULL  | C-310
(4 rows)

 city  
-------
 Kochi
 Pune
 Pune
(3 rows)

 city  
-------
 Surat
(1 row)

 id | two_groups 
----+------------
  1 |          1
  2 |          3
  3 |          5
(3 rows)

"""The statements of ../same-sql/joins_sets.sql on MySQL 8.4.11, as the mysql client prints them."""

from mysql_server import mysql_file

mysql_file("../same-sql/joins_sets.sql")

+------+-------+
| name | room  |
+------+-------+
| Asha | A-101 |
| Ravi | B-204 |
+------+-------+
Warning (Code 4119): Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.
+-------+
| city  |
+-------+
| Kochi |
| Pune  |
| Pune  |
+-------+
+-------+
| city  |
+-------+
| Surat |
+-------+
ERROR 1235 (42000) at line 19: This version of MySQL doesn't yet support 'GROUPS'

• FULL JOIN. PostgreSQL and SQLite returned four rows: three students and the room nobody holds. MySQL returned two rows and no error. It has no full join, and FULL is not a reserved word there, so it read FULL as a short name for students and ran an inner join; warning 4119 is the only sign. The outer joins lesson shows a full join MySQL can run.
• INTERSECT ALL and EXCEPT ALL. PostgreSQL and MySQL keep duplicates when asked, so Pune appears twice in the intersection and Surat alone is left by the difference. SQLite has INTERSECT and EXCEPT only without ALL.
• GROUPS frames. A GROUPS frame counts groups of equal values instead of rows. PostgreSQL and SQLite run it; MySQL answers error 1235, “This version of MySQL doesn’t yet support ‘GROUPS’”.

Quoted names, upserts and RETURNING

-- Run unchanged on SQLite, PostgreSQL and MySQL: quoted names, two upsert spellings and RETURNING.
CREATE TABLE stock (sku VARCHAR(20) PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO stock VALUES ('RICE-1KG', 40);

-- 1. Double quotes and backticks around a column name.
SELECT "qty" AS double_quoted FROM stock;
SELECT `qty` AS backticked FROM stock;

-- 2. Two spellings of "insert, or update the row that is already there".
INSERT INTO stock VALUES ('RICE-1KG', 35) ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty;
INSERT INTO stock VALUES ('RICE-1KG', 30) AS new ON DUPLICATE KEY UPDATE qty = new.qty;
SELECT sku, qty FROM stock;

-- 3. The new row, returned by the INSERT itself.
INSERT INTO stock VALUES ('DAL-1KG', 12) RETURNING sku, qty;

┌───────────────┐
│ double_quoted │
├───────────────┤
│ 40            │
└───────────────┘
┌────────────┐
│ backticked │
├────────────┤
│ 40         │
└────────────┘
┌──────────┬─────┐
│   sku    │ qty │
├──────────┼─────┤
│ RICE-1KG │ 35  │
└──────────┴─────┘
┌─────────┬─────┐
│   sku   │ qty │
├─────────┼─────┤
│ DAL-1KG │ 12  │
└─────────┴─────┘

Parse error near line 11: near "AS": syntax error

"""The statements of ../same-sql/writes.sql on PostgreSQL 18.6, as psql prints them."""

from pg_server import psql_file

psql_file("../same-sql/writes.sql")

 double_quoted 
---------------
            40
(1 row)

ERROR:  syntax error at or near "AS"
LINE 1: SELECT `qty` AS backticked FROM stock;
                     ^
ERROR:  syntax error at or near "AS"
LINE 1: INSERT INTO stock VALUES ('RICE-1KG', 30) AS new ON DUPLICAT...
                                                  ^
   sku    | qty 
----------+-----
 RICE-1KG |  35
(1 row)

   sku   | qty 
---------+-----
 DAL-1KG |  12
(1 row)

"""The statements of ../same-sql/writes.sql on MySQL 8.4.11, as the mysql client prints them."""

from mysql_server import mysql_file

mysql_file("../same-sql/writes.sql")

+---------------+
| double_quoted |
+---------------+
| qty           |
+---------------+
+------------+
| backticked |
+------------+
|         40 |
+------------+
ERROR 1064 (42000) at line 10: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONFLICT (sku) DO UPDATE SET qty = excluded.qty' at line 1
+----------+-----+
| sku      | qty |
+----------+-----+
| RICE-1KG |  30 |
+----------+-----+
ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING sku, qty' at line 1

• Double quotes. In PostgreSQL and SQLite, "qty" names the column and returned 40. In MySQL double quotes make a string, so the query returned the text qty, without any error. MySQL treats " as a name quote only when ANSI_QUOTES is in its SQL mode.
• Backticks. MySQL’s own name quotes also work in SQLite, which accepts them for compatibility, and are a syntax error in PostgreSQL. A name that needs no quotes at all, in lower case without spaces or keywords, works in every engine.
• Upserts. PostgreSQL and SQLite write “insert, or update the row that is already there” as ON CONFLICT (sku) DO UPDATE, and MySQL as AS new ON DUPLICATE KEY UPDATE. Each engine refused the other spelling, so rice ended at 35 in PostgreSQL and SQLite and at 30 in MySQL.
• RETURNING. PostgreSQL and SQLite hand back the inserted row from the INSERT itself. MySQL has no RETURNING; LAST_INSERT_ID() gives the key it generated last on the same connection.

Dates, regular expressions and JSON

-- Run unchanged on SQLite, PostgreSQL and MySQL: month arithmetic, regular expressions and JSON.
-- 1. One month after the last day of January, in three spellings.
SELECT DATE '2026-01-31' + INTERVAL '1' MONTH AS standard_form;
SELECT date('2026-01-31', '+1 month') AS sqlite_form;
SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS mysql_form;

-- 2. Is 'KA-01' two capital letters, a dash and digits?
SELECT 'KA-01' ~ '^[A-Z]{2}-[0-9]+$' AS tilde;
SELECT regexp_like('KA-01', '^[A-Z]{2}-[0-9]+$') AS regexp_like_fn;
SELECT 'KA-01' REGEXP '^[A-Z]{2}-[0-9]+$' AS regexp_op;

-- 3. The city name inside a JSON document.
SELECT json_extract('{"city": {"name": "Pune"}}', '$.city.name') AS json_extract_fn;
SELECT JSON_VALUE('{"city": {"name": "Pune"}}', '$.city.name') AS json_value_fn;
SELECT '{"city": {"name": "Pune"}}' -> 'city' ->> 'name' AS arrows;

┌─────────────┐
│ sqlite_form │
├─────────────┤
│ 2026-03-03  │
└─────────────┘
┌─────────────────┐
│ json_extract_fn │
├─────────────────┤
│ Pune            │
└─────────────────┘
┌────────┐
│ arrows │
├────────┤
│ Pune   │
└────────┘

Parse error near line 3: near "+": syntax error
Parse error near line 5: near "1": syntax error
Parse error near line 8: near "~": syntax error
Parse error near line 9: no such function: regexp_like
Parse error near line 10: no such function: REGEXP
Parse error near line 14: no such function: JSON_VALUE

"""The statements of ../same-sql/dates_text_json.sql on PostgreSQL 18.6, as psql prints them."""

from pg_server import psql_file

psql_file("../same-sql/dates_text_json.sql")

    standard_form    
---------------------
 2026-02-28 00:00:00
(1 row)

ERROR:  function date(unknown, unknown) does not exist
LINE 1: SELECT date('2026-01-31', '+1 month') AS sqlite_form;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR:  syntax error at or near "1"
LINE 1: SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS mysql_for...
                                               ^
 tilde 
-------
 t
(1 row)

 regexp_like_fn 
----------------
 t
(1 row)

ERROR:  syntax error at or near "'^[A-Z]{2}-[0-9]+$'"
LINE 1: SELECT 'KA-01' REGEXP '^[A-Z]{2}-[0-9]+$' AS regexp_op;
                              ^
ERROR:  function json_extract(unknown, unknown) does not exist
LINE 1: SELECT json_extract('{"city": {"name": "Pune"}}', '$.city.na...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 json_value_fn 
---------------
 Pune
(1 row)

ERROR:  operator is not unique: unknown -> unknown
LINE 1: SELECT '{"city": {"name": "Pune"}}' -> 'city' ->> 'name' AS ...
                                            ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

"""The statements of ../same-sql/dates_text_json.sql on MySQL 8.4.11, as the mysql client prints them."""

from mysql_server import mysql_file

mysql_file("../same-sql/dates_text_json.sql")

+---------------+
| standard_form |
+---------------+
| 2026-02-28    |
+---------------+
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', '+1 month') AS sqlite_form' at line 1
+------------+
| mysql_form |
+------------+
| 2026-02-28 |
+------------+
ERROR 1064 (42000) at line 8: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '~ '^[A-Z]{2}-[0-9]+$' AS tilde' at line 1
+----------------+
| regexp_like_fn |
+----------------+
|              1 |
+----------------+
+-----------+
| regexp_op |
+-----------+
|         1 |
+-----------+
+-----------------+
| json_extract_fn |
+-----------------+
| "Pune"          |
+-----------------+
+---------------+
| json_value_fn |
+---------------+
| Pune          |
+---------------+
ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-> 'city' ->> 'name' AS arrows' at line 1

• A month later. The standard spelling, DATE '2026-01-31' + INTERVAL '1' MONTH, runs in PostgreSQL and MySQL, and both stop at the last day of February; PostgreSQL returns a timestamp, MySQL a date. SQLite has neither DATE literals nor INTERVAL. Its own date('2026-01-31', '+1 month') counts the missing days on into March and returns 2026-03-03, as the dates lesson explains.
• Regular expressions. PostgreSQL has the ~ operator and, from version 15, regexp_like(). MySQL has REGEXP and REGEXP_LIKE(). SQLite parses REGEXP but leaves the matching to a regexp() function that the application must supply, and sql.js supplies none, so both forms failed here.
• JSON. JSON_VALUE is the SQL standard’s way to take one value out of a document; PostgreSQL 18.6 and MySQL 8.4.11 have it and SQLite 3.49.1 does not. json_extract runs in SQLite and MySQL but answers differently: SQLite returned the text Pune, MySQL the JSON string "Pune", quotes included. The arrows -> and ->> exist in all three with different rules: SQLite took them on plain text, PostgreSQL wants a json or jsonb value, and MySQL accepts them only after a column name.

Transactions and defaults

-- Run unchanged on SQLite, PostgreSQL and MySQL: CREATE TABLE and a failed statement inside a transaction.
CREATE TABLE wallet (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO wallet VALUES (1, 300), (2, 1000);

-- 1. Is CREATE TABLE undone by ROLLBACK?
BEGIN;
CREATE TABLE audit_log (note VARCHAR(40));
ROLLBACK;
SELECT count(*) AS audit_log_rows FROM audit_log;

-- 2. The debit breaks the CHECK, the credit works, then COMMIT.
BEGIN;
UPDATE wallet SET balance = balance - 500 WHERE id = 1;
UPDATE wallet SET balance = balance + 500 WHERE id = 2;
COMMIT;
SELECT sum(balance) AS total FROM wallet;

┌───────┐
│ total │
├───────┤
│ 1800  │
└───────┘

Parse error near line 9: no such table: audit_log
Runtime error near line 13: CHECK constraint failed: balance >= 0

"""The statements of ../same-sql/transactions.sql on PostgreSQL 18.6, then its default isolation level."""

from pg_server import psql, psql_file

psql_file("../same-sql/transactions.sql")
psql("SHOW transaction_isolation;")

ERROR:  relation "audit_log" does not exist
LINE 1: SELECT count(*) AS audit_log_rows FROM audit_log;
                                               ^
ERROR:  new row for relation "wallet" violates check constraint "wallet_balance_check"
DETAIL:  Failing row contains (1, -200).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
 total 
-------
  1300
(1 row)

 transaction_isolation 
-----------------------
 read committed
(1 row)

"""The statements of ../same-sql/transactions.sql on MySQL 8.4.11, then its default isolation level."""

from mysql_server import mysql, mysql_file

mysql_file("../same-sql/transactions.sql")
mysql("SELECT @@transaction_isolation;")

+----------------+
| audit_log_rows |
+----------------+
|              0 |
+----------------+
ERROR 3819 (HY000) at line 13: Check constraint 'wallet_chk_1' is violated.
+-------+
| total |
+-------+
|  1800 |
+-------+
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

• CREATE TABLE and ROLLBACK. PostgreSQL and SQLite undid the new table with the rest of the transaction. MySQL commits implicitly before and after such statements, so audit_log survived the ROLLBACK, empty.
• An error, then COMMIT. The debit broke the CHECK constraint. SQLite and MySQL undid only that statement, kept the credit at COMMIT and printed a total of 1800, money that came from nowhere. PostgreSQL refused every statement after the error and rolled the whole transaction back, so the total stayed 1300. The transactions lesson shows the code that makes all three safe.
• Isolation. PostgreSQL starts each transaction at read committed and MySQL at REPEATABLE-READ. SQLite runs every transaction as serializable, since it lets only one connection write at a time.

MySQL habits to unlearn

Older MySQL code often numbers rows with a user variable assigned inside the select list:

"""MySQL 8.4.11: a numbering habit from older MySQL code, and the form every engine runs."""

from mysql_server import mysql

mysql("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer VARCHAR(20) NOT NULL);
INSERT INTO orders VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena');

-- Numbering rows with a user variable assigned inside the SELECT.
SET @n := 0;
SELECT customer, @n := @n + 1 AS rn FROM orders ORDER BY id;

-- The same numbers from a window function.
SELECT customer, row_number() OVER (ORDER BY id) AS rn FROM orders ORDER BY id;
""")

+----------+------+
| customer | rn   |
+----------+------+
| Asha     |    1 |
| Ravi     |    2 |
| Meena    |    3 |
+----------+------+
Warning (Code 1287): Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
+----------+----+
| customer | rn |
+----------+----+
| Asha     |  1 |
| Ravi     |  2 |
| Meena    |  3 |
+----------+----+

MySQL 8.4 still runs it, with warning 1287: assigning variables inside expressions is deprecated and will be removed. Its manual also says the order in which such expressions are evaluated is undefined. row_number() OVER (ORDER BY id) gives the same numbers in MySQL 8 and later, PostgreSQL and SQLite. Two more habits travel badly: || written as OR, which only MySQL reads that way, and backticks around names, which PostgreSQL rejects.

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on a
Unix socket in that folder (no TCP port), sends the SQL to psql, then stops the server and deletes the folder, so
every run starts from an empty database and prints the same text. The cluster uses UTF-8 with PostgreSQL's builtin
C.UTF-8 locale (Unicode upper and lower case, text sorted by code point) and UTC; every other setting is
PostgreSQL's default. psql shows NULL as NULL; the rest of the output is psql's own, except that the
"psql:<stdin>:<line>:" in front of each message is left out.

PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6).
"""

import atexit
import os
import pwd
import re
import shutil
import subprocess
import sys

BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

# Short names: a Unix socket path may not be longer than about 100 characters.
_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "pg")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "s")
_env = dict(os.environ, PGHOST=_sock, PGUSER="postgres", PGDATABASE="postgres", LC_ALL="C", LANG="C", TZ="UTC")
_running = False


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _running
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_sock)
    _quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale-provider=builtin", "--builtin-locale=C.UTF-8", "--locale=C", "--no-sync", "--no-instructions")
    options = f"-k {_sock} -c listen_addresses='' -c fsync=off -c timezone=UTC"
    _quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _running = True
    atexit.register(_stop)


def _stop() -> None:
    global _running
    if _running:
        subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _running = False
    shutil.rmtree(_base, ignore_errors=True)


def psql(sql: str) -> None:
    """Sends a block of SQL to psql and prints what it printed, errors included, in order."""
    if not _running:
        _start()
    run = subprocess.run(
        [os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off", "-P", "null=NULL"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(WHERE.sub("", run.stdout))
    sys.stdout.flush()


def psql_file(path: str) -> None:
    """Sends the SQL of a file to psql (see psql)."""
    with open(path, encoding="utf-8") as f:
        psql(f.read())

"""Runs SQL on a private, throwaway MySQL 8.4.11 server and prints what the mysql client printed.

Each run makes a new data folder in its own temporary folder (mysqld --initialize-insecure), starts a server that
listens only on a Unix socket in that folder (--skip-networking: no TCP port), creates the database "shop", sends the
SQL to the mysql client, then shuts the server down and deletes the folder, so every run starts from an empty
database. The server keeps MySQL's defaults (the SQL mode, utf8mb4 with its default collation, REPEATABLE READ);
only the time zone is fixed to UTC. The client prints tables with borders, each warning after the statement that
caused it (--show-warnings), and carries on after an error (--force), so the output shows every statement's answer
in order.

MySQL 8.4.11 comes from the folder where mise installs it (mise install mysql@8.4.11).
"""

import atexit
import os
import pwd
import shutil
import subprocess
import sys
import time

HOME = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "mysql", "8.4.11")
BIN = os.path.join(HOME, "bin")

_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "my")
_data = os.path.join(_base, "d")
_sock = os.path.join(_base, "m.sock")
_server = None


def _client(*args: str, sql: str = "") -> subprocess.CompletedProcess:
    return subprocess.run(
        [os.path.join(BIN, "mysql"), "--no-defaults", f"--socket={_sock}", "-uroot", *args],
        input=sql, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )


def _start() -> None:
    global _server
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    server = [
        os.path.join(BIN, "mysqld"), "--no-defaults", f"--basedir={HOME}", f"--datadir={_data}",
        f"--socket={_sock}", "--skip-networking", "--mysqlx=OFF", f"--pid-file={os.path.join(_base, 'pid')}",
        f"--log-error={os.path.join(_base, 'error.log')}", "--skip-log-bin", "--performance-schema=OFF",
        "--default-time-zone=+00:00",
    ]
    subprocess.run(server[:4] + ["--initialize-insecure", f"--log-error={os.path.join(_base, 'init.log')}"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    _server = subprocess.Popen(server, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    atexit.register(_stop)
    for _ in range(600):
        if _client("-e", "CREATE DATABASE shop").returncode == 0:
            return
        time.sleep(0.1)
    raise RuntimeError("the MySQL server did not start")


def _stop() -> None:
    global _server
    if _server is not None:
        subprocess.run([os.path.join(BIN, "mysqladmin"), "--no-defaults", f"--socket={_sock}", "-uroot", "shutdown"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        try:
            _server.wait(timeout=60)
        except subprocess.TimeoutExpired:
            _server.kill()
        _server = None
    shutil.rmtree(_base, ignore_errors=True)


def mysql(sql: str) -> None:
    """Sends a block of SQL to the mysql client and prints its output, warnings and errors in order."""
    if _server is None:
        _start()
    run = _client("--table", "--force", "--unbuffered", "--show-warnings", "shop", sql=sql.strip() + "\n")
    sys.stdout.write(run.stdout)
    sys.stdout.flush()


def mysql_file(path: str) -> None:
    """Sends the SQL of a file to the mysql client (see mysql)."""
    with open(path, encoding="utf-8") as f:
        mysql(f.read())

The table to keep

Every row comes from the runs above.

Where the track explains each group of rows in depth:

• Division: division and remainders.
• ||, concat() and letter case: joining text, and NULL and changing case.
• True and false: the types you will use most.
• = and LIKE with capitals: letter case.
• NULLs in a sort and row limits: where NULLs go and taking the first rows.
• Bare columns: grouped or aggregated; aliases: aliases in HAVING.
• FULL JOIN: MySQL 8.4 has no FULL OUTER JOIN; INTERSECT and EXCEPT: in each engine.
• Quotes around names: single for text, double for names.
• Month arithmetic: adding days and months.
• DDL and errors in a transaction: changing the schema and when a statement fails.

Two tools help while you port. The SQL formatter parses a query in the dialect you choose and can point at a token that dialect does not recognise, such as backticks for PostgreSQL. The SQLite playground lets you try the SQLite column of this table on your own tables.

Your turn

-- This report was written for MySQL 8.4. Make it return the same rows on SQLite.
SELECT city,
       count(*) AS orders,
       round(sum(amount) / count(*), 2) AS avg_amount,
       GROUP_CONCAT(customer ORDER BY customer SEPARATOR ', ') AS customers
FROM orders
GROUP BY city
HAVING avg_amount > 300
ORDER BY avg_amount DESC, city;

# Sample tests: each one builds a small orders table, runs your SQL on it and compares the rows it returns, in order,
# with what the MySQL 8.4 report returns for the same orders.
tests:
  - name: averages that are whole numbers
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES
        (1, 'Pune', 'Ravi', 900), (2, 'Pune', 'Asha', 400), (3, 'Kochi', 'Meena', 700),
        (4, 'Kochi', 'Arjun', 250), (5, 'Jaipur', 'Tara', 600);
    expected:
      columns: [city, orders, avg_amount, customers]
      rows:
        - [Pune, 2, 650, 'Asha, Ravi']
        - [Jaipur, 1, 600, Tara]
        - [Kochi, 2, 475, 'Arjun, Meena']
  - name: averages with a fraction, and a city at exactly 300 left out
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES
        (1, 'Pune', 'Asha', 400), (2, 'Pune', 'Ravi', 901), (3, 'Kochi', 'Zoya', 334),
        (4, 'Kochi', 'Arjun', 333), (5, 'Kochi', 'Meena', 334), (6, 'Surat', 'Dev', 300);
    expected:
      columns: [city, orders, avg_amount, customers]
      rows:
        - [Pune, 2, 650.5, 'Asha, Ravi']
        - [Kochi, 3, 333.67, 'Arjun, Meena, Zoya']
  - name: one city with one order
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL, customer TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 'Patna', 'Zoya', 1000);
    expected:
      columns: [city, orders, avg_amount, customers]
      rows:
        - [Patna, 1, 1000, Zoya]

Interview questions

Fresher · Why is 7 / 2 equal to 3 in PostgreSQL and SQLite but 3.5000 in MySQL, and how do you get 3.5 in all three?

PostgreSQL and SQLite divide an integer by an integer as integers and drop the fraction, so 7 / 2 is 3, and a negative result is cut toward zero. MySQL’s / always returns a decimal, with four more decimal places than its first operand has by default, so it prints 3.5000; its DIV operator is the one that divides to an integer. To get the same answer everywhere, make one operand a decimal before dividing: 7 * 1.0 / 2, or CAST(7 AS DECIMAL(10, 2)) / 2. The same trap hides in averages and percentages written as sum(x) / count(*): they come out as whole numbers in PostgreSQL and SQLite until one side is a decimal.

Fresher · Where do NULLs go in ORDER BY amount in PostgreSQL, MySQL and SQLite, and how do you get the same order everywhere?

In an ascending sort, PostgreSQL treats NULL as larger than every value and puts it last, while MySQL and SQLite treat it as smaller and put it first; a descending sort flips each of them. PostgreSQL and SQLite accept NULLS FIRST and NULLS LAST, but MySQL 8.4 does not. A form that works in all three is to sort by a NULL test first: ORDER BY amount IS NULL, amount puts the NULLs last, and ORDER BY amount IS NOT NULL, amount puts them first. Paging queries need this most, because a page boundary that moves with the engine shows some rows twice and others never.

Key takeaways

• A moved statement is refused, gives a different answer, or works. Refusals are easy to fix; different answers are found only by running real queries on real data in the new engine.
• Integer division, ||, concat() with NULL, letter case in comparisons and where NULLs sort differ silently.
• Bare columns, aliases in HAVING, FETCH FIRST, RETURNING, INTERSECT ALL and GROUPS frames are accepted by some engines and refused by others; MySQL even runs FULL JOIN as an inner join.
• Upserts, row limits, regular expressions, month arithmetic and JSON are spelled differently in each engine.
• PostgreSQL rolls back DDL and aborts a transaction after an error; MySQL commits around DDL and, like SQLite, keeps the statements that worked.