Both engines speak SQL, but defaults and features diverge. Teams pick Postgres for JSONB, window functions maturity, and extension ecosystem; MySQL remains common in LAMP stacks—see MySQL track for that dialect.
Syntax and type differences (sample)
-- Postgres: BOOLEAN, SERIAL, RETURNING
CREATE TABLE demo (
id SERIAL PRIMARY KEY,
active BOOLEAN NOT NULL DEFAULT true,
meta JSONB
);
INSERT INTO demo (meta) VALUES ('{"k": 1}')
RETURNING id, meta;Practice: Copy SQL into psql, a local PostgreSQL server, or DB Fiddle (PostgreSQL dialect). Compare output with the lesson.
When Postgres often wins
- Complex queries (CTEs, window functions, lateral joins)
- Semi-structured data with JSONB indexes
- Geospatial (PostGIS) or strict SQL semantics
When MySQL may win
Legacy hosting, specific replication tooling, or team expertise. Portable SQL from SQL track still applies—learn dialect quirks on the engine you deploy.
Important interview questions and answers
- Q: RETURNING clause?
A: Postgres can return inserted/updated rows from DML—handy for ORMs and APIs without a second SELECT. - Q: AUTO_INCREMENT in MySQL vs Postgres?
A: MySQL AUTO_INCREMENT; Postgres SERIAL/BIGSERIAL or IDENTITY/GENERATED columns.
Self-check
- Which clause returns new row ids after INSERT in Postgres?
- Name one JSON type native to Postgres.
Tip: RETURNING and JSONB are interview favorites—practice them in psql early.
Interview prep
- RETURNING benefit?
Returns affected rows from DML without a second SELECT.
- JSONB vs JSON?
JSONB is binary, indexable, and preferred for queries.