snippetsqlModeratepending

SQL UPSERT -- insert or update in one statement

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

UPSERTON CONFLICTINSERT OR REPLACEMERGEatomicrace condition

postgresqlmysqlsqlite

Problem

Need to insert a row if it does not exist, or update it if it does. Two separate queries create race conditions under concurrent access.

Solution

Use database-specific UPSERT syntax for atomic insert-or-update operations.

Code Snippets

UPSERT across different databases

-- PostgreSQL: ON CONFLICT
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1,
  last_login = NOW();

-- MySQL: ON DUPLICATE KEY
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  login_count = login_count + 1;

-- SQLite: ON CONFLICT (same as PostgreSQL)
INSERT INTO kv (key, value) VALUES ('setting', 'dark')
ON CONFLICT (key) DO UPDATE SET value = excluded.value;

-- PostgreSQL: DO NOTHING (insert if not exists)
INSERT INTO tags (name) VALUES ('rust')
ON CONFLICT DO NOTHING;

Revisions (0)

No revisions yet.