PostgreSQL and MySQL differences

Opening your lesson

Most SQL you write runs on both, but a few habits don't travel. The database here follows PostgreSQL, so MySQL-only syntax fails, which makes it a good place to practice translating.

Task	MySQL	PostgreSQL (and here)
Quote a name	`order`	`"order"`
Skip and take	`LIMIT 5, 10`	`LIMIT 10 OFFSET 5`
Join text in a group	`GROUP_CONCAT(x)`	`STRING_AGG(x, ',')`
NULL fallback	`IFNULL(a, b)`	`COALESCE(a, b)` (works in both)
Text match ignoring case	`LIKE` (case-insensitive by default)	`ILIKE`
Days between dates	`DATEDIFF(b, a)`	`b - a`
Format a date	`DATE_FORMAT(d, '%Y-%m')`	`TO_CHAR(d, 'YYYY-MM')` (here: `strftime`)

Two behavior differences matter more than syntax: MySQL's / always returns decimals while PostgreSQL divides whole numbers to a whole number, and MySQL in some modes lets you select columns that aren't in GROUP BY. Code that relies on either gives different results on the other database.

SELECT customer_id, STRING_AGG(CAST(order_id AS VARCHAR), ',' ORDER BY order_id) AS orders
FROM orders
GROUP BY customer_id;

The PostgreSQL way to list each customer's order ids in one cell.

Choose your character

Your Work Gym account