snippetModeratepending

SQL upsert patterns — INSERT ON CONFLICT / ON DUPLICATE KEY

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

upsertINSERT ON CONFLICTON DUPLICATE KEYMERGEatomic insert update

linux

Problem

Need to insert a row if it doesn't exist, or update it if it does. Without upsert, you need SELECT then INSERT or UPDATE, which has race conditions under concurrent access.

Solution

Database-specific upsert syntax for PostgreSQL, MySQL, and SQLite. Atomic operation that avoids race conditions.

Code Snippets

Upsert patterns for PostgreSQL, MySQL, and SQLite

-- PostgreSQL: INSERT 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();

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

-- MySQL: ON DUPLICATE KEY UPDATE
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: same as PostgreSQL syntax
INSERT INTO config (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET
  value = excluded.value;

-- PostgreSQL: bulk upsert with UNNEST
INSERT INTO metrics (name, value, updated_at)
SELECT * FROM UNNEST(
  ARRAY['cpu', 'mem', 'disk'],
  ARRAY[0.75, 0.60, 0.45],
  ARRAY[NOW(), NOW(), NOW()]
)
ON CONFLICT (name) DO UPDATE SET
  value = EXCLUDED.value,
  updated_at = EXCLUDED.updated_at;

Revisions (0)

No revisions yet.