MySQL vs Postgres: what changes in the interview

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

The job ad says MySQL, and you practised in Postgres. Or the other way round. About ten small differences trip people up in live rounds. Know them, and you can switch dialects mid-answer without blinking.

You'll learn to

• Translate paging, auto-increment ids, quotes and NULL helpers between the two
• Write an upsert both ways: ON CONFLICT and ON DUPLICATE KEY
• Swap string and date functions, and know why GROUP BY is stricter in Postgres

Learn

Same SQL, different edges

Most of SQL is the same in both: SELECT, JOIN, GROUP BY, HAVING, subqueries, CTEs, and window functions (MySQL 8 and later). The differences sit at the edges: paging, auto-numbered ids, quoting, NULL helpers, upserts, string and date functions, and how strict GROUP BY is.

In an interview, write the dialect the company uses. If you are unsure, say it: "I'll write Postgres; in MySQL this line would be LIMIT 10, 5." That one sentence shows you know both. This lesson runs Postgres; the MySQL side is in the compare tables and callouts.

Paging, ids and quotes

Paging the price list

-- Page 2 of the price list, 3 products per page
SELECT name, price
FROM products
ORDER BY price DESC, product_id
LIMIT 3 OFFSET 3;

Page 2 skips the first 3 rows and takes the next 3: Puzzle, Throw Pillow and Wireless Earbuds. MySQL accepts the same line, or LIMIT 3, 3. Always ORDER BY something unique when you page, or a row can show up on two pages.

Auto-numbered ids, the Postgres way

CREATE TABLE tickets (
  ticket_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subject    VARCHAR(60) NOT NULL
);

INSERT INTO tickets (subject)
VALUES ('Refund request'), ('Late delivery')
RETURNING ticket_id, subject;

Postgres numbers the rows 1 and 2 and RETURNING hands the new ids straight back. In MySQL you would write ticket_id INT AUTO_INCREMENT PRIMARY KEY and read the id with LAST_INSERT_ID(). SERIAL is the older Postgres shorthand for the same idea.

Upsert: insert, or add to the row that is there

INSERT INTO stock (product_id, qty)
VALUES (2, 5), (4, 7)
ON CONFLICT (product_id)
DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

SELECT * FROM stock ORDER BY product_id;

Product 2 already had 10, so it becomes 15. Product 4 is new, so it is inserted with 7. EXCLUDED is the row you tried to insert; stock.qty is the row already in the table. ON CONFLICT … DO NOTHING skips the clash instead.

MySQL writes the same upsert as INSERT INTO stock (product_id, qty) VALUES (2, 5), (4, 7) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);. Newer versions prefer an alias: … VALUES (2, 5) AS new ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty. It fires on any unique key, while Postgres names the conflict target. REPLACE INTO is not the same: it deletes the old row and inserts a new one.

Strings, dates and lists

String and date functions in Postgres

SELECT order_id,
       'Order #' || order_id AS label,
       CONCAT('Courier ', courier_id) AS courier,
       TO_CHAR(ordered_at, 'YYYY-MM') AS month,
       DATE '2025-07-15' - ordered_at::DATE AS days_ago,
       (ordered_at + INTERVAL '3 days')::DATE AS ship_by
FROM orders
WHERE order_id IN (1004, 1005)
ORDER BY order_id;

Order 1005 has no courier. CONCAT skips the NULL and prints Courier ; 'Courier ' || courier_id would print NULL. In MySQL it is the other way round: CONCAT with a NULL argument returns NULL.

GROUP BY is strict in Postgres

Postgres rejects a SELECT column that is neither grouped nor inside an aggregate: SELECT city, name, COUNT(*) FROM customers GROUP BY city fails, because one city has many names. Which name should it show?

Old MySQL (and MySQL with ONLY_FULL_GROUP_BY turned off) runs it and shows any name from the group, with no warning. Answers built on that are wrong in a quiet way.

One helpful exception: if you GROUP BY a table's primary key, Postgres lets you select that table's other columns, since the key decides them. Interview habit: group by the id and the columns you show.

The error, and the fix

SELECT city, name, COUNT(*)
FROM customers
GROUP BY city;

Postgres says column "customers.name" must appear in the GROUP BY clause or be used in an aggregate function. Either group by name too, or aggregate it: STRING_AGG(name, ', ').

Double quotes are the classic dialect slip. WHERE status = "delivered" works in MySQL, where "delivered" is a string. In Postgres it means a column called delivered, and you get column "delivered" does not exist. Use single quotes for text in both.

Interviewers rarely mark you down for using the other dialect, but they notice wrong results: integer division, CONCAT with NULL, and a loose GROUP BY. Name the dialect you are writing, and mention the one line that would change.

In one line

Postgres vs MySQL: LIMIT n OFFSET m (not LIMIT m, n), identity not AUTO_INCREMENT, ON CONFLICT not ON DUPLICATE KEY, COALESCE not IFNULL, 'text' not "text", 7 / 2 is 3.

Interview corner

How do you write an upsert in Postgres and in MySQL?

Postgres: INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col (or DO NOTHING). MySQL: INSERT … ON DUPLICATE KEY UPDATE col = VALUES(col), or with a row alias in newer versions. Both need a unique key or primary key to detect the clash; MySQL fires on any unique key.

What is the Postgres version of AUTO_INCREMENT?

An identity column, id INT GENERATED ALWAYS AS IDENTITY, or the older SERIAL. Both use a sequence. To get the new id back, Postgres has INSERT … RETURNING id; MySQL uses LAST_INSERT_ID().

Why does a GROUP BY query run in MySQL but fail in Postgres?

It selects a column that is neither grouped nor aggregated. Postgres refuses (unless you grouped by the table's primary key), while MySQL without ONLY_FULL_GROUP_BY picks any value from the group. Group by every shown column or aggregate it.