Tags
BareSQL2
BareSQL Audit6
- A Quarterly Access Review Finds a New Table of Bank Accounts That Every Role Can Read
- A Support View Showed Card Numbers to Every Support Login
- BareSQL Launches With an Access Audit for PostgreSQL and a Portability Check for SQL
- How BareSQL Audit Finds Every Column a PostgreSQL Login Can Read
- How BareSQL Audit Was Tested Against Planted Leaks on Six PostgreSQL Versions
- What an AI Support Agent Could Read Before Its Database Login Went Live
BareSQL Portable5
- BareSQL Launches With an Access Audit for PostgreSQL and a Portability Check for SQL
- How BareSQL Portable Finds SQL That Answers Differently on Four Engines
- How BareSQL Portable Was Tested on Four Real Database Engines
- Queries That Pass Their Tests on SQLite and Break on PostgreSQL in Production
- What Changed in the Answers When a MySQL App Moved to PostgreSQL
Case Studies5
- A Quarterly Access Review Finds a New Table of Bank Accounts That Every Role Can Read
- A Support View Showed Card Numbers to Every Support Login
- Queries That Pass Their Tests on SQLite and Break on PostgreSQL in Production
- What an AI Support Agent Could Read Before Its Database Login Went Live
- What Changed in the Answers When a MySQL App Moved to PostgreSQL
Dates2
Explainers2
Functions and Operators2
Inserts and Updates6
- INSERT IGNORE Becomes INSERT OR IGNORE on SQLite and ON CONFLICT DO NOTHING on PostgreSQL
- MySQL Refuses an UPDATE Whose Subquery Reads the Same Table
- MySQL Refuses INSERT RETURNING While PostgreSQL and SQLite Accept It
- SQLite Has No TRUNCATE Statement
- UPDATE With a Join Needs Different Syntax on MySQL and PostgreSQL
- Upsert Needs ON CONFLICT on PostgreSQL and ON DUPLICATE KEY UPDATE on MySQL
NULL and Sorting3
Numbers8
- CAST to INTEGER Fails on MySQL and Rounds Differently on SQLite and PostgreSQL
- Dividing Decimals Gives Six Decimal Places on MySQL and Sixteen on PostgreSQL
- Division by Zero Is NULL on SQLite and MySQL and an Error on PostgreSQL
- Integer Division Drops the Fraction on SQLite and PostgreSQL and Keeps It on MySQL
- LOG With One Argument Is Base 10 on PostgreSQL and the Natural Logarithm on MySQL
- REAL on PostgreSQL and FLOAT on MySQL Are 4-Byte Floats That Drop Digits
- SQLite Keeps DECIMAL Values as Floats and Sums of Prices Pick Up Rounding Errors
- The Caret Operator Is a Power on PostgreSQL and a Bitwise XOR on MySQL
Queries10
- Columns Missing From GROUP BY Work on SQLite and MariaDB and Fail on PostgreSQL and MySQL
- Comparing a Boolean With 1 Works on MySQL and SQLite and Fails on PostgreSQL
- COUNT DISTINCT With Two Columns Works on MySQL and Fails on PostgreSQL and SQLite
- DISTINCT ON Works on PostgreSQL Only
- FETCH FIRST ROWS ONLY Works on PostgreSQL and MariaDB and Fails on MySQL and SQLite
- FULL OUTER JOIN Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB
- LIMIT With a Comma Works on MySQL and SQLite and Fails on PostgreSQL
- MySQL and MariaDB Refuse LIMIT Inside an IN Subquery
- Only SQLite Lets WHERE Use a Name From the Select List
- PostgreSQL Refuses Select List Names in HAVING
Schemas10
- Column Types Like DATETIME and BYTEA Exist on Some SQL Engines Only
- MySQL and MariaDB on Linux Treat Table Names as Case Sensitive
- MySQL Refuses a Key on a TEXT Column Without a Prefix Length
- MySQL Refuses a Plain DEFAULT Value on TEXT Columns
- MySQL Refuses CREATE INDEX IF NOT EXISTS
- MySQL Table Options Like ENGINE and CHARSET Fail on PostgreSQL and SQLite
- No Auto Increment Column Syntax Works on Every SQL Engine
- SQLite Has No CREATE OR REPLACE VIEW
- SQLite Stores Text in INTEGER Columns and Ignores VARCHAR Lengths
- VARCHAR Without a Length Fails on MySQL and MariaDB
SQL Differences58
- A Backslash in LIKE Escapes Percent and Underscore Everywhere Except SQLite
- A Backslash Is an Escape Character in MySQL Strings and a Plain Character in PostgreSQL
- Adding 1 to a Date Gives the Next Day on PostgreSQL and a Number on MySQL and SQLite
- Backticks Around Names Work on MySQL and SQLite and Fail on PostgreSQL
- CAST to CHAR Cuts a Value to One Character on PostgreSQL
- CAST to DATE Returns the Number 2024 on SQLite Because It Has No Date Type
- CAST to INTEGER Fails on MySQL and Rounds Differently on SQLite and PostgreSQL
- CHAR Columns Come Back Padded With Spaces on PostgreSQL
- Column Types Like DATETIME and BYTEA Exist on Some SQL Engines Only
- Columns Missing From GROUP BY Work on SQLite and MariaDB and Fail on PostgreSQL and MySQL
- Comparing a Boolean With 1 Works on MySQL and SQLite and Fails on PostgreSQL
- Comparing a Text Column With the Number 0 Matches Codes Like abc on MySQL and Fails on PostgreSQL
- CONCAT With a NULL Argument Returns NULL on MySQL and Skips It on PostgreSQL
- COUNT DISTINCT With Two Columns Works on MySQL and Fails on PostgreSQL and SQLite
- DISTINCT ON Works on PostgreSQL Only
- Dividing Decimals Gives Six Decimal Places on MySQL and Sixteen on PostgreSQL
- Division by Zero Is NULL on SQLite and MySQL and an Error on PostgreSQL
- Double Dash Comments Need a Space After Them on MySQL and MariaDB
- Double Quotes Name a Column on PostgreSQL and Make a String on MySQL
- FETCH FIRST ROWS ONLY Works on PostgreSQL and MariaDB and Fails on MySQL and SQLite
- FULL OUTER JOIN Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB
- GREATEST Skips NULL on PostgreSQL and Returns NULL on MySQL
- Hash Comments Work on MySQL and MariaDB and Become an Operator on PostgreSQL
- INSERT IGNORE Becomes INSERT OR IGNORE on SQLite and ON CONFLICT DO NOTHING on PostgreSQL
- Integer Division Drops the Fraction on SQLite and PostgreSQL and Keeps It on MySQL
- LENGTH Counts Bytes on MySQL and MariaDB and Characters on PostgreSQL and SQLite
- LIKE Ignores Letter Case on SQLite and MySQL and Respects It on PostgreSQL
- LIMIT With a Comma Works on MySQL and SQLite and Fails on PostgreSQL
- LOG With One Argument Is Base 10 on PostgreSQL and the Natural Logarithm on MySQL
- MySQL and MariaDB on Linux Treat Table Names as Case Sensitive
- MySQL and MariaDB Refuse LIMIT Inside an IN Subquery
- MySQL Compares Text Without Regard to Letter Case and PostgreSQL Compares It Exactly
- MySQL Refuses a Key on a TEXT Column Without a Prefix Length
- MySQL Refuses a Plain DEFAULT Value on TEXT Columns
- MySQL Refuses an UPDATE Whose Subquery Reads the Same Table
- MySQL Refuses CREATE INDEX IF NOT EXISTS
- MySQL Refuses INSERT RETURNING While PostgreSQL and SQLite Accept It
- MySQL Table Options Like ENGINE and CHARSET Fail on PostgreSQL and SQLite
- No Auto Increment Column Syntax Works on Every SQL Engine
- NULL Sorts First on SQLite and MySQL and Last on PostgreSQL
- NULLS FIRST and NULLS LAST Fail on MySQL and MariaDB
- Only SQLite Lets WHERE Use a Name From the Select List
- PostgreSQL Refuses Select List Names in HAVING
- PostgreSQL Returns Column Names in Lower Case Unless You Quote Them
- REAL on PostgreSQL and FLOAT on MySQL Are 4-Byte Floats That Drop Digits
- SQL Functions That Exist on Some Engines Only and What to Write Instead
- SQL Operators That Exist on Some Engines Only and What to Write Instead
- SQLite Has No CREATE OR REPLACE VIEW
- SQLite Has No TRUNCATE Statement
- SQLite Keeps DECIMAL Values as Floats and Sums of Prices Pick Up Rounding Errors
- SQLite LOWER and UPPER Change Only the Letters A to Z
- SQLite Stores Text in INTEGER Columns and Ignores VARCHAR Lengths
- SUBSTR From Position 0 Returns Text on SQLite and PostgreSQL and Nothing on MySQL
- The Caret Operator Is a Power on PostgreSQL and a Bitwise XOR on MySQL
- The Double Pipe Operator Joins Text on PostgreSQL and SQLite and Means OR on MySQL
- UPDATE With a Join Needs Different Syntax on MySQL and PostgreSQL
- Upsert Needs ON CONFLICT on PostgreSQL and ON DUPLICATE KEY UPDATE on MySQL
- VARCHAR Without a Length Fails on MySQL and MariaDB
Syntax5
- Backticks Around Names Work on MySQL and SQLite and Fail on PostgreSQL
- Double Dash Comments Need a Space After Them on MySQL and MariaDB
- Double Quotes Name a Column on PostgreSQL and Make a String on MySQL
- Hash Comments Work on MySQL and MariaDB and Become an Operator on PostgreSQL
- PostgreSQL Returns Column Names in Lower Case Unless You Quote Them
Testing2
Text12
- A Backslash in LIKE Escapes Percent and Underscore Everywhere Except SQLite
- A Backslash Is an Escape Character in MySQL Strings and a Plain Character in PostgreSQL
- CAST to CHAR Cuts a Value to One Character on PostgreSQL
- CHAR Columns Come Back Padded With Spaces on PostgreSQL
- Comparing a Text Column With the Number 0 Matches Codes Like abc on MySQL and Fails on PostgreSQL
- CONCAT With a NULL Argument Returns NULL on MySQL and Skips It on PostgreSQL
- LENGTH Counts Bytes on MySQL and MariaDB and Characters on PostgreSQL and SQLite
- LIKE Ignores Letter Case on SQLite and MySQL and Respects It on PostgreSQL
- MySQL Compares Text Without Regard to Letter Case and PostgreSQL Compares It Exactly
- SQLite LOWER and UPPER Change Only the Letters A to Z
- SUBSTR From Position 0 Returns Text on SQLite and PostgreSQL and Nothing on MySQL
- The Double Pipe Operator Joins Text on PostgreSQL and SQLite and Means OR on MySQL