Appendices
Appendix I. PostgreSQL versus MySQL Syntax Reference
Chapter 17's ledger, expanded to the full working map: every platform difference this book met, with the PostgreSQL and MySQL spellings side by side and the chapter where it was taught.
Statements and clauses
| Need | PostgreSQL | MySQL 8.0 |
|---|
| Concatenation | 'a' || 'b' | CONCAT('a', 'b') |
| Case-insensitive LIKE | ILIKE 'x' | LIKE 'x' (default collation) or LOWER(col) LIKE ... |
| Regex match | col ~ 'pat' / ~* | REGEXP_LIKE(col, 'pat') / col REGEXP 'pat' |
| LIMIT / offset | LIMIT n OFFSET m | LIMIT m, n or LIMIT n OFFSET m |
| Standard fetch | OFFSET m ROWS FETCH NEXT n ROWS ONLY | (not supported) |
| NULL placement in ORDER BY | NULLS FIRST / LAST | none — sort on col IS NULL, col |
| Upsert | ON CONFLICT (k) DO UPDATE SET x = EXCLUDED.x | ON DUPLICATE KEY UPDATE x = VALUES(x) |
| DML result rows | RETURNING col | ROW_COUNT(), LAST_INSERT_ID() |
| INTERSECT / EXCEPT | native | 8.0.31+ (joins before) |
| FULL OUTER JOIN | native | emulate: LEFT JOIN ... UNION ALL ... RIGHT JOIN ... WHERE IS NULL |
| Quoting identifiers | "name" | backquotes (ANSI_QUOTES mode: double quotes) |
| Comments in DDL | COMMENT ON TABLE/COLUMN | COMMENT '...' in-line |
Types and identifiers
| Concept | PostgreSQL | MySQL 8.0 |
|---|
| Exact decimal | NUMERIC(p,s) | DECIMAL(p,s) (synonyms both) |
| Unlimited string | text (preferred) | TEXT tiers |
| Boolean | boolean | TINYINT(1) convention |
| Zone-aware timestamp | timestamptz | TIMESTAMP (UTC-converted, 1970–2038) |
| Literal timestamp | timestamp | DATETIME (1000–9999) |
| UUID | uuid | BINARY(16) / UUID() strings |
| Auto key | GENERATED ... AS IDENTITY / SERIAL | AUTO_INCREMENT |
| Enumerated domain | ENUM type + CHECK (preferred) | ENUM(...) column type |
| Arrays / ranges / domains | native | none (JSON approximates) |
Functions
| Task | PostgreSQL | MySQL |
|---|
| Characters | LENGTH(s) | CHAR_LENGTH(s) |
| Bytes | OCTET_LENGTH(s) | LENGTH(s) |
| Find | POSITION(sub IN s) | LOCATE(sub, s) |
| Split field | split_part(s, ',', n) | SUBSTRING_INDEX(s, ',', n) |
| Date add | d + INTERVAL '1 year' | DATE_ADD(d, INTERVAL 1 YEAR) |
| Date diff | AGE(a, b) (interval) | DATEDIFF(a, b) days; TIMESTAMPDIFF(unit, a, b) |
| Date truncate | date_trunc('month', ts) | DATE_FORMAT(ts, '%Y-%m-01') + cast |
| Format | to_char(d, 'YYYY-MM') | DATE_FORMAT(d, '%Y-%m') |
| Parse | to_date(s, 'YYYY-MM-DD') | STR_TO_DATE(s, '%Y-%m-%d') |
| Conditional value | CASE (also IF in procedures) | CASE and IF(cond, a, b) |
| NULL substitute | COALESCE(a, b) | COALESCE(a, b) / IFNULL(a, b) |
| Grouped string | string_agg(x, ', ' ORDER BY y) | GROUP_CONCAT(x ORDER BY y SEPARATOR ', ') (mind max_len) |
| Grouped array | array_agg(x) | none |
| Conditional count | COUNT(*) FILTER (WHERE c) | SUM(CASE WHEN c THEN 1 ELSE 0 END) |
| Per-group first row | DISTINCT ON (k) ... ORDER BY k, x | ROW_NUMBER() OVER (PARTITION BY k ...) + filter |
| Series of rows | generate_series(1, n) | recursive CTE / numbers table |
| Cast | CAST(x AS t) / x::t | CAST(x AS t) / CONVERT(x, t) |
JSON
| Task | PostgreSQL | MySQL 8.0 |
|---|
| Types | json, jsonb (use jsonb) | JSON |
| Extract (typed / text) | -> 'k' / ->> 'k' | -> '$.k' / ->> '$.k' |
| Longhand extract | jsonb_extract_path | JSON_EXTRACT |
| Containment | @>, ?, ?&, ?| | JSON_CONTAINS, JSON_OVERLAPS |
| Update in place | jsonb_set, || | JSON_SET, JSON_MERGE_PATCH |
| Rows from JSON | jsonb_array_elements | JSON_TABLE |
| JSON from rows | jsonb_agg, jsonb_build_object | JSON_ARRAYAGG, JSON_OBJECTAGG |
| Indexing | GIN on the column (containment) | multi-valued index (8.0.17) on arrays |
Constraints, DDL, and transactions
| Aspect | PostgreSQL | MySQL 8.0 |
|---|
| CHECK enforcement | always | 8.0.16+ |
| Deferred checking | DEFERRABLE INITIALLY DEFERRED | none (immediate) |
| Add FK online | NOT VALID + VALIDATE CONSTRAINT | ALGORITHM=INPLACE, LOCK=NONE online DDL |
| Change column type | ALTER COLUMN ... TYPE t | MODIFY COLUMN t |
| Drop constraint | DROP CONSTRAINT name | DROP INDEX name / DROP FOREIGN KEY name |
| Table DDL review | \d table | SHOW CREATE TABLE table |
| Storage engines | one engine | per-table ENGINE= (InnoDB default) |
| DDL in transactions | transactional (rollback-able) | implicit COMMIT |
| Default isolation | READ COMMITTED | REPEATABLE READ (phantoms blocked by next-key locks) |
| Set isolation | SET SESSION CHARACTERISTICS ... | SET SESSION TRANSACTION ... |
| Deadlock error | SQLSTATE 40P01 | SQLSTATE 40001 (ERROR 1213) |
| Materialized views | native + REFRESH [CONCURRENTLY] | emulate: table + event/trigger |
| Scheduled SQL | cron / pg_cron (external) | CREATE EVENT (built-in scheduler) |
| Index extras | partial, expression, INCLUDE, GIN/GiST/BRIN/hash | prefix, functional key parts, FULLTEXT, spatial |
| Bulk load | COPY (server) / \copy (client) | LOAD DATA [LOCAL] INFILE, INTO OUTFILE |
| File sandbox | server OS user / path visibility | secure_file_priv / local_infile |
The portability method (Chapter 17 recap)
- Write the standard spelling where one exists.
- Catalog every divergence here in the project's dialect document.
- Test on both platforms during development — the parity proof (Chapter 28 Lab 10).
- Confine dialect to one layer (the repository / query module).
- Treat differences in rounding and ordering as expected (AVG precision, NULL placement) and differences in rows as bugs.