Tag
SQL differences
SELECT customer_id, name, SUM(total) FROM orders GROUP BY customer_id is the kind of query that runs for years on SQLite or MariaDB. Point the same code at PostgreSQL or …
SQL differences
WHERE active = 1 is the usual way to test a flag on MySQL, and SQLite and MariaDB read it the same way. Moved to PostgreSQL, the query stops with an error. On SQLite, …
SQL differences
COUNT(DISTINCT customer_id, product_id) counts the distinct pairs on MySQL and MariaDB. SQLite and PostgreSQL take a single expression in COUNT(DISTINCT ...) and refuse …
SQL differences
DISTINCT ON (customer_id) is a neat way to keep each customer’s latest order on PostgreSQL, and none of the other three engines has it. SQLite, MySQL and MariaDB report a …
SQL differences
FETCH FIRST 10 ROWS ONLY is the standard way to ask for the top ten, and PostgreSQL and MariaDB accept it. SQLite and MySQL refuse the query. MariaDB added FETCH FIRST in …
SQL differences
Matching two lists, such as invoices and payments, to see what is missing on either side calls for a FULL OUTER JOIN. PostgreSQL and SQLite run it, and MySQL and MariaDB …
SQL differences
Paging code written for MySQL often says LIMIT 20, 10: skip 20 rows, return 10. SQLite and MariaDB read it the same way. PostgreSQL refuses it. In LIMIT a, b the first …
SQL differences
Finding the customers behind the ten latest orders with WHERE id IN (SELECT customer_id FROM orders ORDER BY created_at DESC LIMIT 10) works on SQLite and PostgreSQL. …
SQL differences
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
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 …