findyourpilot/.agents/skills/supabase-postgres-best-practices/references/data-upsert.md

title, impact, impactDescription, tags

title	impact	impactDescription	tags
Use UPSERT for Insert-or-Update Operations	MEDIUM	Atomic operation, eliminates race conditions	upsert, on-conflict, insert, update

Use UPSERT for Insert-or-Update Operations

Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.

Incorrect (check-then-insert race condition):

-- Race condition: two requests check simultaneously
select * from settings where user_id = 123 and key = 'theme';
-- Both find nothing

-- Both try to insert
insert into settings (user_id, key, value) values (123, 'theme', 'dark');
-- One succeeds, one fails with duplicate key error!

Correct (atomic UPSERT):

-- Single atomic operation
insert into settings (user_id, key, value)
values (123, 'theme', 'dark')
on conflict (user_id, key)
do update set value = excluded.value, updated_at = now();

-- Returns the inserted/updated row
insert into settings (user_id, key, value)
values (123, 'theme', 'dark')
on conflict (user_id, key)
do update set value = excluded.value
returning *;

Insert-or-ignore pattern:

-- Insert only if not exists (no update)
insert into page_views (page_id, user_id)
values (1, 123)
on conflict (page_id, user_id) do nothing;

Reference: INSERT ON CONFLICT