Tag
SQL differences
Searching for the text 50_off with LIKE '50\_off' works on PostgreSQL, MySQL and MariaDB, where the backslash escapes the underscore. SQLite treats the backslash as an …
SQL differences
A Windows path written as 'C:\temp\new' reads back on MySQL and MariaDB with a tab and a line break in it. PostgreSQL and SQLite keep the backslashes as typed. 'C:\\temp' …
SQL differences
due_date + 1 reads like the day after, and on PostgreSQL that is what it gives. On MySQL, MariaDB and SQLite the answer is a plain number. MySQL and MariaDB turn the date …
SQL differences
MySQL examples often quote every name in backticks, as in SELECT `name` FROM `people`. Copied into a PostgreSQL project, the query fails. `name` is MySQL’s way of quoting …
SQL differences
CAST(order_id AS CHAR) looks like a safe way to turn a number into text. On PostgreSQL CHAR without a length means one character, so order 123 comes back as '1'. …
SQL differences
Filtering orders with CAST(created_at AS DATE) = '2024-05-06' works on PostgreSQL, MySQL and MariaDB. SQLite has no date type, turns the CAST into a number and keeps only …
SQL differences
Turning a price into a whole number with CAST(5.7 AS INTEGER) gives 5 on SQLite and 6 on PostgreSQL and MariaDB. MySQL doesn’t run it at all. SQLite drops the fraction …
SQL differences
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. …
SQL differences
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
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
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
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 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
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
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 …
SQL differences
In a line like SELECT price --discount, left over from debugging, SQLite and PostgreSQL read --discount as a comment. MySQL and MariaDB add the discount to the price …
SQL differences
A query copied from PostgreSQL that puts a column name in double quotes runs on MySQL without an error, and never reads the column. Standard SQL quotes names with double …
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
GREATEST(sale_price, list_price) picks the larger of two prices. When one of them is NULL, PostgreSQL still picks the other price, and no other engine does. GREATEST(1, …
SQL differences
MySQL and MariaDB skip everything from a # to the end of the line. On SQLite and PostgreSQL that # is still part of the statement. Scripts written for the mysql client …
SQL differences
Loading a file of events that may hold rows you already have calls for an insert that skips duplicates. MySQL and MariaDB spell it INSERT IGNORE and SQLite INSERT OR …
SQL differences
You write an average as SUM(stock) / COUNT(*), try it on SQLite and get 3. MySQL gives 3.3333 for the same rows. Both are doing what they were built to do, and they …
SQL differences
A check that a name is at most 20 characters long, written with LENGTH(name), lets 20 letters through on SQLite and PostgreSQL. MySQL and MariaDB count bytes instead, so …
SQL differences
A search box that runs WHERE name LIKE 'ann%' finds Ann on SQLite, MySQL and MariaDB. On PostgreSQL it misses her, because LIKE there compares letters exactly. SQLite’s …
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
A score or a scale built on LOG() shifts when the code moves to another engine, because LOG(100) doesn’t mean the same logarithm everywhere. LOG(100) is 2 on SQLite and …
SQL differences
Code that creates a table as Users and later reads users works on SQLite and PostgreSQL. On MySQL or MariaDB running on Linux it fails, since users is another table that …
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
Sign-ups as ann@example.com and Ann@example.com are two users on PostgreSQL and SQLite. On MySQL and MariaDB a unique key on the email column refuses the second sign-up. …
SQL differences
A TEXT column as a primary key is ordinary on SQLite and PostgreSQL. MySQL and MariaDB refuse it, and MySQL refuses a plain index or unique key on TEXT as well. SQLite …
SQL differences
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 …
SQL differences
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
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
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
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
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
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
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
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 …
SQL differences
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. …
SQL differences
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
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
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
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
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
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
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
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 …
SQL differences
Code ported from a language that counts from 0 often ends up with SUBSTR(code, 0, 3). SQLite and PostgreSQL return the first two characters. MySQL and MariaDB return an …
SQL differences
A growth formula written as rate ^ years works on PostgreSQL. On MySQL and MariaDB it still runs and gives numbers that have nothing to do with growth: 2 ^ 3 is 1 there. …
SQL differences
Code that builds labels with first_name || ' ' || last_name works on PostgreSQL and SQLite. Move it to MySQL or MariaDB and every label comes back as 0, with no error to …
SQL differences
Copying prices from a staging table with a join takes UPDATE ... FROM on SQLite and PostgreSQL and UPDATE ... JOIN on MySQL and MariaDB, and neither runs on all four. The …
SQL differences
Saving a setting that may or may not exist yet takes one statement on every engine, though not the same one on all four. SQLite and PostgreSQL use ON CONFLICT ... DO …
SQL differences
Schemas written for PostgreSQL often declare names and notes as plain VARCHAR, with no length. MySQL and MariaDB refuse that CREATE TABLE, since they want a length on …