SQL differences
PostgreSQL Returns Column Names in Lower Case Unless You Quote Them
SELECT SUM(amount) AS Total gives a column named Total on SQLite, MySQL and MariaDB. PostgreSQL calls it total. SQLite, MySQL and MariaDB return names as written. …
Posts
case studies
Queries That Pass Their Tests on SQLite and Break on PostgreSQL in Production
This case study uses a demo project in BareSQL’s code repository: a made-up booking app with a schema of two tables and eleven queries, written the way an app tested on …
SQL differences
REAL on PostgreSQL and FLOAT on MySQL Are 4-Byte Floats That Drop Digits
Measurements kept in a REAL column on PostgreSQL, or in a FLOAT column on MySQL or MariaDB, come back with fewer digits than went in. The same type names keep them all on …
SQL differences
SQL Functions That Exist on Some Engines Only and What to Write Instead
IFNULL, NOW, LEFT and STRING_AGG are everyday functions, and each one is missing on at least one of SQLite, PostgreSQL, MySQL and MariaDB. BareSQL Portable knows 78 …
SQL differences
SQL Operators That Exist on Some Engines Only and What to Write Instead
Operators such as ::, ILIKE, DIV and XOR belong to one or two engines, and the others refuse them. BareSQL Portable knows 16 operators like these. Each operator in the …
SQL differences
SQLite Has No CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW lets a migration change a view in one step on PostgreSQL, MySQL and MariaDB. On SQLite the statement is a syntax error. SQLite can only drop a view …
SQL differences
SQLite Has No TRUNCATE Statement
Test setups often empty their tables with TRUNCATE TABLE between runs. PostgreSQL, MySQL and MariaDB have it, and SQLite refuses it. SQLite empties a table quickly with a …
SQL differences
SQLite Keeps DECIMAL Values as Floats and Sums of Prices Pick Up Rounding Errors
A shop keeps its prices in a DECIMAL(10, 2) column and checks that an order’s lines add up to its total. On SQLite that check can fail for an order of 0.10 and 0.20. …
SQL differences
SQLite LOWER and UPPER Change Only the Letters A to Z
Searching names with LOWER(name) = LOWER('Émile') works on PostgreSQL, MySQL and MariaDB. On SQLite, LOWER changes only A to Z, so É stays É and a stored émile never …
SQL differences
SQLite Stores Text in INTEGER Columns and Ignores VARCHAR Lengths
A test suite on SQLite passes with a quantity of 'abc', or 'Germany' in a VARCHAR(2) country column. PostgreSQL, MySQL and MariaDB refuse both values, so the bug shows up …