Lesson 4 · Foundations

Writing data & UPSERT

INSERT, UPDATE, soft delete, upsert — and a first taste of transactions.

Your win: write rows with INSERT/UPDATE, understand the ON CONFLICT upsert your repos use, and explain what a transaction guarantees — the "all-or-nothing" that Part 5 goes deep on.

The three write statements

INSERT INTO emails (email_id, subject, status) VALUES ($1, $2, $3);
UPDATE emails SET status = $2 WHERE email_id = $1;
DELETE FROM emails WHERE email_id = $1;

Anchor — we don't actually DELETE Our repos soft-delete: instead of DELETE, they run UPDATE emails SET deleted_at = now() WHERE … and every read filters deleted_at IS NULL (Lesson 3). The row stays for audit/recovery; it just becomes invisible. Repo-wide convention.

UPSERT: insert-or-update in one statement

Often you want "insert this row, but if it already exists, update it instead" — without a race between a SELECT and an INSERT. That's INSERT … ON CONFLICT … DO UPDATE, and it's atomic:¹

internal/spike/.../repositories/email.go:37

INSERT INTO emails AS e (...) VALUES (...)
ON CONFLICT ON CONSTRAINT pk__emails       -- if the PK already exists…
DO UPDATE SET sg_message_id = EXCLUDED.sg_message_id, ..., type = EXCLUDED.type
WHERE e.deleted_at IS NULL;                -- (…update it instead)

EXCLUDED is the row you tried to insert — so SET col = EXCLUDED.col means "use the new value". DO NOTHING is the other option (ignore the conflict).

Transactions: all-or-nothing

A transaction groups statements so they all commit together or not at all (BEGIN … COMMIT, or ROLLBACK on failure). That's the A in ACID — atomicity. Our Go code wraps related writes in one transaction via the ExecInTx closure (from the Go course, Lesson 13):

internal/conversationmgmt/.../support_conversation_modifier_learner_create_conversation.go:79

database.ExecInTx(ctx, svc.DB, func(ctx, tx pgx.Tx) error {
    svc.SupportConversationRepo.Insert(ctx, tx, conversation)        // both…
    svc.SupportConversationLocationRepo.Insert(ctx, tx, location)    // …or neither
    return nil   // nil → COMMIT;  non-nil → ROLLBACK
})

Why it matters If the second insert fails, the first is rolled back too — you never end up with a conversation that has no location. The same tx is threaded into both repo calls so they share one transaction. How concurrent transactions see each other (isolation levels) is Part 5.

PostgreSQL Tutorial — UPSERT + the docs on transactions

A worked ON CONFLICT example, then the official transactions tutorial for BEGIN/COMMIT/ROLLBACK.

→ postgresqltutorial.com — UPSERT
→ postgresql.org/docs — transactions

Check yourself (from memory)

Q1. An UPSERT (INSERT … ON CONFLICT) lets you…

Atomically insert, or update the existing row on a conflict — no SELECT-then-INSERT race. EXCLUDED = the attempted row.

Q2. A transaction guarantees a group of writes is…

Atomicity: commit together or roll back together. Our ExecInTx rolls back both inserts if either fails.

Q3. Instead of DELETE, our repos usually…

Soft delete — mark the row and filter

deleted_at
      IS NULL

on reads. Nothing is physically removed.

What does ON CONFLICT … DO UPDATE do, and how does ExecInTx group writes?

recall, then click to reveal

INSERT … ON CONFLICT ON CONSTRAINT pk__emails DO UPDATE SET
    col = EXCLUDED.col

is an UPSERT — it inserts, but if that would violate the named constraint (the PK exists) it updates the existing row instead, atomically (EXCLUDED = the row you tried to insert).

database.ExecInTx(ctx, db,
    func(ctx, tx){...})

wraps several writes in one transaction — begin, run the closure, COMMIT if it returns nil else ROLLBACK — so e.g. a conversation and its location either both succeed or both roll back (atomicity, the "A" in ACID).

🎓 That's Part 1 — SQL foundations You can read the schema, query it, and write to it — the SQL you'll meet in every repo. Part 2 goes under the surface: how Postgres actually stores those rows (pages & tuples) and the big idea that makes it concurrent — MVCC.

Ready for Part 2 (internals & data structures), or a quick mixed quiz across Lessons 1–4 first? Ask me.

1. PostgreSQL — INSERT … ON CONFLICT.