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.
- Author
- Amey-Thakur ↗
- Repository
- AI-SKILLS ↗
- Licence
- MIT
- Last repository change
- 05.09.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 05.09.2026, without references to retired models or APIs.
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
- Method
- Boundaries
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…