SQL differences
CHAR Columns Come Back Padded With Spaces on PostgreSQL
An order status kept in a CHAR(10) column compares as expected in SQL on every engine. In the application code, though, a check for paid orders fails on PostgreSQL alone. …
Posts
SQL differences
Column Types Like DATETIME and BYTEA Exist on Some SQL Engines Only
A schema with DATETIME and JSONB columns runs on SQLite without complaint. Both types fail on at least one of the other engines. SQLite accepts nearly any type name, so a …
SQL differences
Columns Missing From GROUP BY Work on SQLite and MariaDB and Fail on PostgreSQL and MySQL
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
Comparing a Boolean With 1 Works on MySQL and SQLite and Fails on PostgreSQL
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
Comparing a Text Column With the Number 0 Matches Codes Like abc on MySQL and Fails on PostgreSQL
Product codes are text, and a query that forgets the quotes, WHERE code = 0, still runs on MySQL and MariaDB. There it returns codes that have nothing to do with 0. MySQL …
SQL differences
CONCAT With a NULL Argument Returns NULL on MySQL and Skips It on PostgreSQL
A customer list built with CONCAT(first_name, ' ', last_name) looks complete on PostgreSQL and SQLite. Moved to MySQL or MariaDB, the same query returns NULL for some …
SQL differences
COUNT DISTINCT With Two Columns Works on MySQL and Fails on PostgreSQL and SQLite
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 Works on PostgreSQL Only
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
Dividing Decimals Gives Six Decimal Places on MySQL and Sixteen on PostgreSQL
Split an invoice of 80.50 three ways and MySQL and MariaDB hand back 26.833333, PostgreSQL 26.8333333333333333 and SQLite a floating point number. Most of the time nobody …
SQL differences
Division by Zero Is NULL on SQLite and MySQL and an Error on PostgreSQL
A report that divides revenue by visits fails on PostgreSQL the first time a page has no visits. On SQLite, MySQL and MariaDB the same row quietly shows NULL. PostgreSQL …