Transactions, Performance, and Production SQLLesson 6.5

UPSERT, soft deletes, and common application SQL patterns

INSERT ON CONFLICT, UPSERT pattern, soft delete with deleted_at, partial index for soft deletes, UPDATE returning, bulk insert

UPSERT: Insert or Update Atomically

UPSERT inserts a row if it doesn't exist, or updates it if it does — atomically, in one statement. No race condition between checking and inserting.

-- PostgreSQL: ON CONFLICT DO UPDATE
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
  theme    = EXCLUDED.theme,
  language = EXCLUDED.language,
  updated_at = NOW();

Soft Deletes With deleted_at

Instead of physically deleting rows (losing history), add a deleted_at timestamp. NULL means active; a timestamp means deleted:

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- All queries must filter active rows
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index keeps active-user queries fast
CREATE INDEX idx_users_active
  ON users(email)
  WHERE deleted_at IS NULL;

RETURNING Clause

PostgreSQL's RETURNING returns the inserted or updated rows without a second SELECT query:

INSERT INTO orders (customer_id, amount)
VALUES (5, 299.99)
RETURNING id, created_at;

Ready to practice?

MCQs · Coding challenges · Mini project