Upsert
Definition
Upsert creates a new record or updates it if a matching one already exists.
A unique key decides whether to insert or update. Database systems offer their own commands for this, such as INSERT with conflict handling in PostgreSQL.
Simply explained
Like a card index: If the customer number already exists, the card is changed; otherwise, a new one is added.
Why it matters in practice
Repeated data imports can be done without unnecessary duplicates.
Example from business
Hypothetical example: A product import updates existing items via their stable article number.
Limits and pitfalls
- A wrong key overwrites foreign records; omitted fields and simultaneous accesses require clear rules.
Origin & history
Upsert combines the classic database operations insert and update.
Use in mid-sized companies
- Controlled synchronization of master data from multiple systems.
Frequently asked questions
Does upsert automatically detect semantic duplicates?
No, it uses the defined identity or conflict rule.
Sources
- INSERT und ON CONFLICT · PostgreSQL: INSERT und ON CONFLICT –
Editorially reviewed on: