The same everyday task, written for SQL Server, MySQL and PostgreSQL, side by side. Use it when you move between databases, read a tutorial written for a different one, or port a query from one system to another.
MySQL entries are for MySQL 8 and later (most also work in MariaDB); PostgreSQL entries are for version 14 and later. Where a SQL Server feature needs a recent version, the version is noted. The MySQL and PostgreSQL entries were tested on MariaDB 10.11 and PostgreSQL 16.
Want it on your desk? All 31 tasks and the three gotchas fit on one printable page.
Selecting and paging
| Task | SQL Server | MySQL / MariaDB | PostgreSQL |
|---|---|---|---|
| Top 5 rows | SELECT TOP (5) ... ORDER BY x | ... ORDER BY x LIMIT 5 | ... ORDER BY x LIMIT 5 |
| Page 3 (20 per page) | ORDER BY x OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY | ORDER BY x LIMIT 20 OFFSET 40 | ORDER BY x LIMIT 20 OFFSET 40 |
| Two-way IF | IIF(cond, a, b) | IF(cond, a, b) | CASE WHEN cond THEN a ELSE b END |
| Replace NULL | COALESCE(x, 0) / ISNULL(x, 0) | COALESCE(x, 0) / IFNULL(x, 0) | COALESCE(x, 0) |
| NULL-safe equal | a IS NOT DISTINCT FROM b (2022+) | a <=> b | a IS NOT DISTINCT FROM b |
| Case-insensitive LIKE | LIKE (default collation) | LIKE (default collation) | ILIKE |
| Regex match | REGEXP_LIKE(s, 'p') (2025+) | s REGEXP 'p' | s ~ 'p' |
Text
| Task | SQL Server | MySQL / MariaDB | PostgreSQL |
|---|---|---|---|
| Join text | CONCAT(a, b) or a + b | CONCAT(a, b) | a || b or concat(a, b) |
| Length (characters) | LEN(s) | CHAR_LENGTH(s) | length(s) |
| Find position | CHARINDEX('x', s) | LOCATE('x', s) | strpos(s, 'x') |
| Part of a string | SUBSTRING(s, 2, 3) | SUBSTRING(s, 2, 3) | substring(s, 2, 3) |
| Rows to one list | STRING_AGG(col, ', ') | GROUP_CONCAT(col SEPARATOR ', ') | string_agg(col, ', ') |
| Split ‘a,b,c’ to rows | STRING_SPLIT(s, ',') | JSON_TABLE(...) trick | string_to_table(s, ',') |
Dates
| Task | SQL Server | MySQL / MariaDB | PostgreSQL |
|---|---|---|---|
| Now | SYSDATETIME() / GETDATE() | NOW() | now() |
| Today (no time) | CAST(GETDATE() AS date) | CURDATE() | current_date |
| Add 7 days | DATEADD(day, 7, d) | d + INTERVAL 7 DAY | d + INTERVAL '7 days' |
| Days between | DATEDIFF(day, d1, d2) | DATEDIFF(d2, d1) | d2 - d1 |
| Year of a date | YEAR(d) | YEAR(d) | EXTRACT(YEAR FROM d) |
| First day of month | DATEFROMPARTS(YEAR(d), MONTH(d), 1) | DATE_FORMAT(d, '%Y-%m-01') | date_trunc('month', d)::date |
| Last day of month | EOMONTH(d) | LAST_DAY(d) | (date_trunc('month', d) + INTERVAL '1 month - 1 day')::date |
| ISO week number | DATEPART(iso_week, d) | WEEK(d, 3) | EXTRACT(WEEK FROM d) |
| UTC to Bahrain time | t AT TIME ZONE 'UTC' AT TIME ZONE 'Arab Standard Time' | CONVERT_TZ(t, '+00:00', '+03:00') | t AT TIME ZONE 'Asia/Bahrain' |
Tables and changes
| Task | SQL Server | MySQL / MariaDB | PostgreSQL |
|---|---|---|---|
| Auto-number column | INT IDENTITY(1,1) | INT AUTO_INCREMENT | INT GENERATED ALWAYS AS IDENTITY |
| True / false column | BIT | BOOLEAN (TINYINT(1)) | boolean |
| Unicode text | NVARCHAR(100) | VARCHAR(100) (utf8mb4) | varchar(100) or text |
| Get the new ID | OUTPUT inserted.id / SCOPE_IDENTITY() | LAST_INSERT_ID() | RETURNING id |
| Insert or update | MERGE or UPDATE + IF @@ROWCOUNT = 0 INSERT | INSERT ... ON DUPLICATE KEY UPDATE | INSERT ... ON CONFLICT (key) DO UPDATE |
| Replace a view | CREATE OR ALTER VIEW v AS ... | CREATE OR REPLACE VIEW v AS ... | CREATE OR REPLACE VIEW v AS ... |
| Run a procedure | EXEC proc @p = 1 | CALL proc(1) | CALL proc(1) |
| See the query plan | Ctrl+M in SSMS / SET STATISTICS IO ON | EXPLAIN ANALYZE SELECT ... | EXPLAIN ANALYZE SELECT ... |
Three Differences That Catch People Out
- Argument order in date differences. SQL Server’s
DATEDIFF(day, start, end)puts the start first; MySQL’sDATEDIFF(end, start)puts the end first. Swap them and the sign flips. - Case sensitivity. SQL Server and MySQL compare text case-insensitively with their default collations; PostgreSQL is case-sensitive, so use
ILIKEorlower(). - Unquoted names. PostgreSQL folds unquoted table and column names to lowercase, so
Customersbecomescustomers. Use lowercase names with underscores to avoid quoting everywhere.
Keep a copy next to your keyboard: download the one-page PDF version of this cheat sheet.
Learn More
- PostgreSQL Explained for Beginners: includes a SQL Server to PostgreSQL translation table.
- MySQL vs PostgreSQL: Which Should You Use?
- SQL Date Functions Explained Simply
- SQL String Functions Explained Simply
- SQL INSERT, UPDATE and DELETE: including upsert in all three databases.