Postgres vs MySQL: The Same Query, Side-by-Side

10 tasks. Same goal, both dialects. Syntax verified against PostgreSQL 17 docs and MySQL 8.4 docs, April 2026.

1. Upsert (insert or update)

PostgreSQL 17

INSERT INTO users (id, email, updated_at)
VALUES (42, 'user@example.com', NOW())
ON CONFLICT (id)
DO UPDATE SET
  email = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at;

MySQL 8.4 LTS

INSERT INTO users (id, email, updated_at)
VALUES (42, 'user@example.com', NOW())
ON DUPLICATE KEY UPDATE
  email = VALUES(email),
  updated_at = VALUES(updated_at);

ON CONFLICT targets a specific constraint and uses EXCLUDED. to reference the new values. MySQL ON DUPLICATE KEY UPDATE uses VALUES() and fires on any unique constraint violation.

2. Returning the inserted row

PostgreSQL 17

INSERT INTO orders (user_id, total)
VALUES (42, 99.99)
RETURNING id, created_at;
-- Returns: id=1001, created_at=2026-04-30...

MySQL 8.4 LTS

INSERT INTO orders (user_id, total)
VALUES (42, 99.99);
-- Must follow up with:
SELECT id, created_at
FROM orders
WHERE id = LAST_INSERT_ID();

Postgres RETURNING clause makes INSERT atomic with the result fetch. MySQL requires a second query with LAST_INSERT_ID(), which is session-scoped but not truly atomic with the INSERT in all patterns.

3. JSON: extract a field

PostgreSQL 17

-- Extract a JSON field with JSONB
SELECT
  data->>'name'       AS name,
  data->'address'->>'city' AS city
FROM users
WHERE data->>'status' = 'active';

-- With GIN index:
CREATE INDEX idx_users_data ON users USING GIN (data);

MySQL 8.4 LTS

-- Extract with JSON_VALUE / JSON_EXTRACT
SELECT
  JSON_VALUE(data, '$.name')             AS name,
  JSON_VALUE(data, '$.address.city')     AS city
FROM users
WHERE JSON_VALUE(data, '$.status') = 'active';

-- Functional index (generated column approach):
ALTER TABLE users
  ADD COLUMN status VARCHAR(20) AS (JSON_VALUE(data, '$.status'));
CREATE INDEX idx_status ON users (status);

Postgres JSONB operators (->, ->>) are concise. GIN index enables arbitrary path queries without schema changes. MySQL requires generated columns + regular indexes for equivalent query support.

4. JSON: filter by value in JSON array

PostgreSQL 17

-- Find users where tags JSON array contains 'postgres'
SELECT id, name
FROM users
WHERE data @> '{"tags": ["postgres"]}';

-- @> uses GIN index automatically

MySQL 8.4 LTS

-- Find rows where JSON array contains 'postgres'
SELECT id, name
FROM users
WHERE JSON_CONTAINS(data, '"postgres"', '$.tags');

-- No simple GIN equivalent; use generated virtual column + fulltext for large tables

Postgres @> (contains) operator uses GIN index for efficient JSON path containment checks. MySQL JSON_CONTAINS works but lacks an equivalent of Postgres GIN for arbitrary containment.

5. Window function: rank within partition

PostgreSQL 17

-- Rank sales reps by revenue within region
SELECT
  name,
  region,
  revenue,
  RANK() OVER (
    PARTITION BY region
    ORDER BY revenue DESC
  ) AS rank_in_region
FROM sales_reps;

MySQL 8.4 LTS

-- Identical syntax in MySQL 8.0+
SELECT
  name,
  region,
  revenue,
  RANK() OVER (
    PARTITION BY region
    ORDER BY revenue DESC
  ) AS rank_in_region
FROM sales_reps;

Window function syntax is identical. Parity reached in MySQL 8.0. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, FIRST_VALUE, LAST_VALUE all work in both.

6. Recursive CTE: tree traversal

PostgreSQL 17

-- Find all descendants of node 1
WITH RECURSIVE subtree AS (
  -- Base case
  SELECT id, parent_id, name
  FROM categories
  WHERE id = 1
  UNION ALL
  -- Recursive step
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;

MySQL 8.4 LTS

-- Identical syntax in MySQL 8.0+
WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name
  FROM categories
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;

Recursive CTE syntax is identical. Parity reached in MySQL 8.0. Both support tree traversal, path enumeration, and graph problems with recursive CTEs.

7. Generate a series

PostgreSQL 17

-- Generate 30 days of dates
SELECT generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  '1 day'::interval
) AS day;

-- Also works for integers:
SELECT generate_series(1, 100) AS n;

MySQL 8.4 LTS

-- MySQL has no generate_series(); use recursive CTE
WITH RECURSIVE days AS (
  SELECT CURDATE() - INTERVAL 30 DAY AS day
  UNION ALL
  SELECT day + INTERVAL 1 DAY
  FROM days
  WHERE day < CURDATE()
)
SELECT day FROM days;

Postgres generate_series() is a built-in set-returning function for dates, timestamps, and integers. MySQL requires a recursive CTE workaround. This affects query ergonomics for time-series gap filling.

8. Range partitioning DDL

PostgreSQL 17

-- Postgres declarative range partitioning (PG 10+)
CREATE TABLE events (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_q1
  PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE events_2026_q2
  PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

MySQL 8.4 LTS

-- MySQL range partitioning
CREATE TABLE events (
  id BIGINT AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  data JSON,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE COLUMNS(created_at) (
  PARTITION q1 VALUES LESS THAN ('2026-04-01'),
  PARTITION q2 VALUES LESS THAN ('2026-07-01'),
  PARTITION q3 VALUES LESS THAN (MAXVALUE)
);
-- Note: MySQL requires partition key in PRIMARY KEY

Postgres declarative partitioning (PG 10+) allows clean inheritance-based partitions with no primary key constraint. MySQL requires the partition key to be part of every unique index including PRIMARY KEY.

9. Auto-increment / sequence

PostgreSQL 17

-- Modern Postgres: GENERATED ALWAYS AS IDENTITY
CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

-- Legacy (still common):
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

MySQL 8.4 LTS

-- MySQL: AUTO_INCREMENT
CREATE TABLE products (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- Get last inserted:
SELECT LAST_INSERT_ID();

Postgres offers both SERIAL (sequence-based shorthand) and the SQL-standard GENERATED ALWAYS AS IDENTITY. MySQL AUTO_INCREMENT is simpler but less flexible. Postgres sequences can be detached and reused across tables.

10. Full-text search

PostgreSQL 17

-- Postgres tsvector full-text search
-- Index:
CREATE INDEX idx_articles_fts ON articles
  USING GIN (to_tsvector('english', title || ' ' || body));

-- Query:
SELECT title, ts_rank(
  to_tsvector('english', title || ' ' || body),
  to_tsquery('english', 'postgres & performance')
) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ to_tsquery('english', 'postgres & performance')
ORDER BY rank DESC;

MySQL 8.4 LTS

-- MySQL InnoDB FULLTEXT search
-- Index (defined at table creation or with ALTER):
ALTER TABLE articles
  ADD FULLTEXT INDEX idx_articles_fts (title, body);

-- Query:
SELECT title,
  MATCH(title, body)
    AGAINST('postgres performance' IN NATURAL LANGUAGE MODE)
    AS relevance
FROM articles
WHERE MATCH(title, body)
    AGAINST('postgres performance' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

Postgres tsvector/tsquery supports language-specific stemming, complex boolean queries (& | ! operators), phrase search, and ranking via ts_rank. MySQL FULLTEXT natural language mode is sufficient for basic search but less flexible. Both lose to dedicated search engines (Elasticsearch, Typesense) at scale.

Feature parity analysis DDL translation guide