1---
2title: Use UPSERT for Insert-or-Update Operations
3impact: MEDIUM
4impactDescription: Atomic operation, eliminates race conditions
5tags: upsert, on-conflict, insert, update
6---
7 
8## Use UPSERT for Insert-or-Update Operations
9 
10Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.
11 
12**Incorrect (check-then-insert race condition):**
13 
14```sql
15-- Race condition: two requests check simultaneously
16select * from settings where user_id = 123 and key = 'theme';
17-- Both find nothing
18 
19-- Both try to insert
20insert into settings (user_id, key, value) values (123, 'theme', 'dark');
21-- One succeeds, one fails with duplicate key error!
22```
23 
24**Correct (atomic UPSERT):**
25 
26```sql
27-- Single atomic operation
28insert into settings (user_id, key, value)
29values (123, 'theme', 'dark')
30on conflict (user_id, key)
31do update set value = excluded.value, updated_at = now();
32 
33-- Returns the inserted/updated row
34insert into settings (user_id, key, value)
35values (123, 'theme', 'dark')
36on conflict (user_id, key)
37do update set value = excluded.value
38returning *;
39```
40 
41Insert-or-ignore pattern:
42 
43```sql
44-- Insert only if not exists (no update)
45insert into page_views (page_id, user_id)
46values (1, 123)
47on conflict (page_id, user_id) do nothing;
48```
49 
50Reference: [INSERT ON CONFLICT](https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT)
51