Skip to content
Skills Library
Programming · Building & coding

upsert-patterns

/upsert-patterns

Insert or update in one statement without races or lost updates, using the engine's conflict handling rather than check-then-write. Use when writing records that may or may not already exist.

Repository
AI-SKILLS ↗
Licence
MIT
Last repository change
05.09.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 05.09.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Insert or update in one statement without races or lost updates, using the engine's conflict handling rather than check-then-write. Use when writing records that may or may not already exist.

Original author description

Insert or update in one statement without races or lost updates, using the engine's conflict handling rather than check-then-write. Use when writing records that may or may not already exist.

How it works

  1. Method
  2. Boundaries
Original SKILL.md headings · 39 lines, 275 words
SKILL.md preview
---
name: upsert-patterns
description: Insert or update in one statement without races or lost updates, using the engine's conflict handling rather than check-then-write. Use when writing records that may or may not already exist.
---

# Upsert patterns

The naive approach, checking whether a row exists and then inserting or
updating, is a race in every concurrent system: two callers both check,
both find nothing, and one insert fails or duplicates. Upsert exists so
the database resolves that atomically.

## Method

1. **Use the engine's conflict clause.** ON CONFLICT, MERGE, or the
   equivalent performs the decision inside one statement, which is what
   makes it safe under concurrency.
2. **Target a real unique constraint.** Conflict handling needs a
   constraint to detect against, so the uniqueness must exist in the
   schema rather than in your intent.
3. **Decide what update means on conflict.** Overwrite everything, merge
   selected columns, or do nothing. Blindly overwriting can discard
   concurrent changes made between read and write (see
   transactions-isolation).
4. **Preserve creation metadata.** created_at and similar columns should
   not be overwritten by the update branch, which is an easy detail to
   miss and hard to notice later.
5. **Make bulk upserts deterministic.** A batch containing two rows with
   the same key needs a defined winner, since some engines error and
   others pick arbitrarily.
6. **Consider idempotency for retried writes.** An upsert keyed on a
   natural or client-supplied key makes a retried request safe, which is
   what you want in any queue or API path (see idempotency).

## Boundaries

- Upsert resolves existence races; it does not resolve semantic
  conflicts about which version of the data is correct.
- Conflict syntax and behaviour differ substantially between engines, so
  this is one of the less portable parts of SQL.
- Heavy upsert traffic on the same keys creates contention, which is a
  throughput problem rather than a correctness one.

Versions

The database has no other skills named upsert-patterns.

Database snapshot: 13.11.2026 · package version 4e0a7c010c43 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100yida-business-rule
    宜搭表单业务关联规则与高级函数配置。用于在表单设置中配置业务关联规则,或使用 INSERT、UPDATE、DELETE、UPSERT 高级函数实现跨表增删改。适用于需要表单间数据联动的场景。
  • 84crud-ops
    Show ScalarDB CRUD operation patterns — builder syntax for Get, Scan, Insert, Upsert, Update, Delete with examples. Use when writing or reviewing code against the ScalarDB CRUD (transactional) API, or when the user asks how to express a read/write in ScalarDB without SQL.
  • 100t-sql-upserts-merge
    Writes an upsert for Azure SQL Database that is still correct when two sessions run it at the same moment, and refuses the MERGE shapes that lose rows. Use when asked to "insert or update", "insert if not exists", "add or update", "make this insert idempotent", "upsert", "write…
  • 84jdbc-ops
    Show ScalarDB JDBC/SQL operation patterns — SELECT, INSERT, UPSERT, UPDATE, DELETE, JOIN, aggregates with examples. Use when writing or reviewing code against the ScalarDB SQL/JDBC interface, or when the user asks what SQL ScalarDB supports (and its limitations).
  • 93writing-sql
    Write SQL queries for Celigo RDBMS exports and imports -- SELECT, INSERT, UPDATE, UPSERT, MERGE, delta, once, and bulk operations across Snowflake, Postgres, MySQL, SQL Server, Oracle, BigQuery, and Redshift. Use when editing rdbms.query or troubleshooting SQL errors.
  • 100mariadb-replace
    MariaDB-specific syntax and behavior for REPLACE — why REPLACE is DELETE-then-INSERT (not an upsert/UPDATE), when it triggers (PRIMARY/UNIQUE conflicts only), its data-loss footguns (unlisted columns reset to DEFAULT, AUTO_INCREMENT burned, ON DELETE cascades and DELETE+INSERT…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.