SQL differences
MySQL Refuses a Plain DEFAULT Value on TEXT Columns
A notes column declared as TEXT DEFAULT '' works on SQLite, PostgreSQL and MariaDB. MySQL refuses the CREATE TABLE. MySQL 8.0.13 and later accept a default written as an …
Posts
SQL differences
MySQL Refuses an UPDATE Whose Subquery Reads the Same Table
Marking rows with UPDATE t SET flag = 1 WHERE id IN (SELECT id FROM t WHERE ...) works on SQLite, PostgreSQL and MariaDB. MySQL refuses an UPDATE or DELETE whose subquery …
SQL differences
MySQL Refuses CREATE INDEX IF NOT EXISTS
Migrations that may run twice often say CREATE INDEX IF NOT EXISTS, and SQLite, PostgreSQL and MariaDB accept it. MySQL stops the migration with a syntax error. MySQL …
SQL differences
MySQL Refuses INSERT RETURNING While PostgreSQL and SQLite Accept It
INSERT ... RETURNING id hands back the new row’s id in the same round trip on SQLite, PostgreSQL and MariaDB. MySQL has no RETURNING and refuses the statement. SQLite …
SQL differences
MySQL Table Options Like ENGINE and CHARSET Fail on PostgreSQL and SQLite
A MySQL schema ends its CREATE TABLE statements with options such as ENGINE=InnoDB DEFAULT CHARSET=utf8mb4. SQLite and PostgreSQL refuse the statement at the first …
SQL differences
No Auto Increment Column Syntax Works on Every SQL Engine
A schema meant for several engines needs ids that number themselves, and no single way of asking for that works on all four. SERIAL comes closest, and on SQLite it fails …
SQL differences
NULL Sorts First on SQLite and MySQL and Last on PostgreSQL
Sort customers by the date of their last order and the ones who never ordered come first on SQLite, MySQL and MariaDB and last on PostgreSQL. The first page of the same …
SQL differences
NULLS FIRST and NULLS LAST Fail on MySQL and MariaDB
ORDER BY shipped_at NULLS LAST is the tidy way to put unshipped orders at the end on PostgreSQL and SQLite. MySQL and MariaDB have no such syntax and refuse the query. …
SQL differences
Only SQLite Lets WHERE Use a Name From the Select List
A query that names price * quantity as total in the select list and then filters on total in WHERE runs on SQLite. PostgreSQL, MySQL and MariaDB refuse it, since the …
SQL differences
PostgreSQL Refuses Select List Names in HAVING
A report of repeat customers names its count of orders n in the select list and filters on that name in HAVING. SQLite, MySQL and MariaDB run it, and PostgreSQL refuses …