PostgreSQL, MySQL, SQL Server, Oracle and SQLite Compared

Concept

SQL is a standard that every product implements partially and extends privately. The core - SELECT, JOIN, GROUP BY, constraints, transactions - is genuinely portable. Everything around the edges is not.

The differences that break code

Limiting rows

Auto generated keys

Strings

-- Concatenation
a || b                          -- standard, PostgreSQL, Oracle, SQLite
CONCAT(a, b)                    -- MySQL, MariaDB (|| means OR by default)
a + b                           -- SQL Server

-- Length
CHAR_LENGTH(s)                  -- standard, MySQL, PostgreSQL
LEN(s)                          -- SQL Server
LENGTH(s)                       -- Oracle, SQLite (bytes in MySQL!)

-- Case sensitivity of =
-- MySQL:      collation driven, usually case INsensitive
-- PostgreSQL: case SENSITIVE (ILIKE exists for patterns)
-- SQL Server: collation driven, usually case insensitive
-- Oracle:     case sensitive

Dates

-- Now
NOW()          -- MySQL, PostgreSQL      GETDATE()   -- SQL Server
CURRENT_DATE   -- standard, most         SYSDATE     -- Oracle

-- Add 7 days
DATE_ADD(d, INTERVAL 7 DAY)     -- MySQL
d + INTERVAL '7 days'           -- PostgreSQL
DATEADD(DAY, 7, d)              -- SQL Server
d + 7                           -- Oracle
DATE(d, '+7 days')              -- SQLite

-- Difference in days
DATEDIFF(a, b)                  -- MySQL: a minus b
DATEDIFF(DAY, b, a)             -- SQL Server: note the reversed argument order
a - b                           -- PostgreSQL, Oracle

Upsert

INSERT ... ON DUPLICATE KEY UPDATE ...    -- MySQL, MariaDB
INSERT ... ON CONFLICT (col) DO UPDATE    -- PostgreSQL, SQLite
MERGE INTO ... WHEN MATCHED THEN ...      -- standard, SQL Server, Oracle

NULL handling

COALESCE(a, b)   -- standard, everywhere. Prefer this.
IFNULL(a, b)     -- MySQL, SQLite
ISNULL(a, b)     -- SQL Server
NVL(a, b)        -- Oracle

Feature availability

Behavioural differences, not just syntax

Important rules

• The core query language is portable. Paging, identity, dates, strings and upserts are not.
• Version matters as much as product: MySQL 5.7 and 8.0 differ more than some separate products do.
• Behavioural differences are more dangerous than syntax ones - syntax errors are loud, behaviour changes are silent.
• Oracle's empty string equals NULL rule breaks assumptions carried from any other product.

Common mistakes

• Copying an answer from the internet without checking which product it targets.
• Porting an application between products and not re checking the isolation level.
• Using LIMIT in code destined for SQL Server or older Oracle.
• Assuming || concatenates on MySQL - it is OR.
• Relying on MySQL's loose GROUP BY, then moving to PostgreSQL.

Best practices

• Write down the target product and version in the project documentation.
• Prefer standard forms - COALESCE, CAST, EXTRACT, CHAR_LENGTH - where one exists.
• Isolate the non portable parts in one layer, so a port touches few files.
• Run tests against the same product and version as production.

Practice

1. Write "the 20 most recent orders, skipping the first 40" for MySQL, PostgreSQL and SQL Server.
2. Write an upsert into customers for MySQL and for PostgreSQL.
3. List three behavioural - not syntactic - differences that would silently change results in a port from PostgreSQL to MySQL.