Can my DB?

INSERT INTO users (id, email)
VALUES (1, 'a@example.com')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email;

• Conflict target must be explicitly specified.
• Supports DO NOTHING as an alternative to DO UPDATE.
• Also supports the SQL-standard MERGE syntax as of PostgreSQL 15.

INSERT INTO users (id, email)
VALUES (1, 'a@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

• Triggered by any unique key violation, not just the primary key.
• VALUES() in UPDATE clause is deprecated in 8.0.20+; use alias syntax instead.
• No support for conditional conflict targets.

INSERT INTO users (id, email)
VALUES (1, 'a@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

• Same syntax as MySQL ON DUPLICATE KEY UPDATE.
• Also supports REPLACE INTO as a delete-then-insert alternative.

MERGE INTO users t
USING (SELECT 1 AS id, 'a@example.com' AS email FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• Uses SQL-standard MERGE syntax.
• Supports WHEN MATCHED and WHEN NOT MATCHED clauses with conditions.

MERGE INTO users AS t
USING (VALUES (1, 'a@example.com')) AS s(id, email)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• Uses MERGE syntax; statement must end with semicolon.
• Supports OUTPUT clause for RETURNING-like behavior.
• MERGE has had concurrency bugs in older versions; check KB articles.

INSERT INTO users (id, email)
VALUES (1, 'a@example.com')
ON CONFLICT(id) DO UPDATE SET email = excluded.email;

• Syntax similar to PostgreSQL but uses lowercase 'excluded' table reference.
• Also supports INSERT OR REPLACE, but that deletes and re-inserts.

MERGE INTO users AS t
USING (VALUES (1, 'a@example.com')) AS s(id, email)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• MERGE statement added in April 2023.
• Supports WHEN MATCHED (UPDATE or DELETE) and WHEN NOT MATCHED (INSERT) clauses.
• No INSERT ... ON CONFLICT syntax; MERGE is the native upsert mechanism.

MERGE INTO users AS t
USING (VALUES (1, 'a@example.com')) AS s(id, email)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• Supports SQL-standard MERGE.
• Also supports multiple WHEN MATCHED clauses with conditions.

MERGE INTO users AS t
USING (SELECT 1 AS id, 'a@example.com' AS email) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• Uses MERGE syntax.
• Supports multiple WHEN clauses.

MERGE INTO users AS t
USING (SELECT 1 AS id, 'a@example.com' AS email) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

• Uses MERGE syntax.
• Source and target must be different tables.