183: INSERT, UPDATE, DELETE, UPSERT, RETURNING, and Safe Writes

Learning outcomes

By the end of this lesson, you can:

explain and apply insert in a realistic implementation;
explain and apply update in a realistic implementation;
explain and apply delete in a realistic implementation;
explain and apply returning in a realistic implementation;
explain and apply upsert in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from a previous project where one of these concerns appeared. Maybe it was creating a record, changing a quantity, handling a retry, or deciding whether a record should be deleted at all. The point is not to memorize a list of SQL keywords. The point is to make a defensible decision inside a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all affect the result.

Terminology

INSERT: Add rows by naming the columns explicitly and supplying parameterized values. Explicit column lists keep the statement tied to the intended contract instead of relying on the table's physical column order.
UPDATE: Change only the rows that are intended to change. The WHERE clause is part of that correctness contract, not an optional filter to add later.
DELETE: Remove or deactivate data according to a deliberate policy. The policy may be a hard delete, a soft delete, an archive, or a domain state transition.
RETURNING: PostgreSQL RETURNING exposes authoritative inserted, updated, or deleted values without requiring a second query. That includes values produced by generated IDs, defaults, timestamps, and triggers.
UPSERT: INSERT ... ON CONFLICT combines an insert attempt with a precisely defined conflict action. It can insert a new row or update an existing row when a specified uniqueness conflict occurs.
Batch and bulk writes: Loading many rows in one operation can reduce round trips, but it also increases lock duration, transaction size, and the cost of recovering from failure.

These terms describe different write decisions, not interchangeable spellings for “change the database.” The useful distinction is the invariant each statement is responsible for and what the caller should learn when the operation does not take the normal path.

Mental model

Treat INSERT, UPDATE, DELETE, UPSERT, RETURNING, and Safe Writes as a design problem with observable inputs, outputs, invariants, and failure modes. A write should express intent precisely, protect against races, and return the authoritative values created by defaults, triggers, or concurrency rules. A strong implementation makes its assumptions visible, narrows uncertainty at system boundaries, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to show why the design is safe.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call. First state what must remain true. Then choose the SQL statement, constraint, transaction boundary, and application behavior that enforce it. This sequence also gives you a debugging path: if the result is wrong, you can ask whether the requirement, constraint, model, implementation, or verification step failed.

Deep dive

1. INSERT

Use INSERT when the operation's intent is to create a new row. Specify the column list explicitly and use parameterized values; both choices make the statement safer and easier to review. Multi-row inserts are efficient for batches, but they still need a size limit and an explicit transaction decision. A large batch is not free: it can hold locks longer, consume more resources, and make a partial failure harder to recover from.

Decision rule: Use insert deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

2. UPDATE

Use UPDATE when an existing row should change. The WHERE clause is part of the correctness contract: without it, the statement can modify every row in the table. Update only the rows that are intended to change, and consider an optimistic version predicate when stale clients must not overwrite newer data. When possible, avoid a read-modify-write sequence. One atomic SQL expression can often enforce the change more safely than reading a value into application code, calculating a replacement, and writing it back later.

Decision rule: Use update deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

3. DELETE

Use DELETE only after deciding what deletion means for the domain. The choice may be a hard delete, a soft delete, an archive, or a domain state transition. These options have different effects on retention, auditability, foreign keys, and future reads. In particular, a soft-delete flag changes uniqueness rules, ordinary queries, indexes, and foreign-key expectations. It should not be adopted casually as a universal substitute for deletion.

Decision rule: Use delete deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

4. RETURNING

After a write, application code often needs the row that PostgreSQL actually stored. PostgreSQL RETURNING exposes authoritative inserted, updated, or deleted values without an extra query, including generated IDs and timestamps. This avoids a second round trip and prevents the application from pretending it knows a value that was chosen by a default, trigger, or concurrent database rule.

Decision rule: Use returning deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

5. UPSERT

When two requests try to create the same logical record, a check-then-insert sequence can race: both requests can observe that the row is absent before either insert commits. INSERT ... ON CONFLICT can resolve that uniqueness race atomically. Define the conflict target and update semantics carefully so that an unrelated conflict is not silently converted into an update. The database constraint identifies the conflict; the DO NOTHING or DO UPDATE action defines what the application means by it.

Decision rule: Use upsert deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

6. Batch and bulk writes

Bulk loading can reduce round trips, which is useful for imports and other high-volume work, but it increases lock duration, transaction size, and failure-recovery cost. Bound batches rather than allowing an unbounded input to become one transaction. Preserve idempotency for retryable import jobs so that a timeout or client retry does not create duplicate effects.

Decision rule: Use batch and bulk writes deliberately when they make the contract or invariant easier to prove. If they only reduce round trips while hiding transaction or recovery assumptions, prefer a more explicit design.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence. Then list the input and output contracts and identify which of the concepts above owns each failure mode. For example, an input boundary may reject malformed data, a database constraint may reject a duplicate, and a transaction may determine whether several writes become visible together.

The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns can make a happy-path demo look shorter, but it makes edge cases much harder to reason about and can leave a client-side check standing in for a database guarantee.

The following query is not a write, but it is a useful reminder that a realistic database operation is more than a keyword. Its join, filter, grouping, and ordering each express part of the requested result, and its execution still depends on the schema and query plan:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. This is the level of explanation expected in a senior code review or technical interview. In a write implementation, also identify whether the database returned the authoritative row, whether zero affected rows is meaningful, and whether a retry is safe.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence, such as logs, metrics, an execution plan, or a reproducible test.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and the network input is untrusted. Parameterization protects SQL values from injection, but it does not decide authorization, validate domain rules, or make an unsafe write target safe.

Guided lab

Implement create, update, and delete for an inventory item. Use RETURNING, an atomic quantity adjustment, and an UPSERT for a tenant-scoped external key. Prove that duplicate concurrent creates do not produce two records. Your proof should rely on the appropriate uniqueness constraint and conflict behavior, not only on an application-level “check first” branch.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

The lab should leave you with more than working statements. Be able to explain which layer rejects each invalid case, what RETURNING gives the caller, how the quantity update remains atomic, and what happens when two transactions use the same tenant-scoped external key.

Edge cases and failure modes

INSERT: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
UPDATE: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
DELETE: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
RETURNING: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
UPSERT: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.

For each case, do not stop at “the query failed.” Record the expected row count or returned row, the error category, the transaction outcome, and whether a retry is safe. Those observations make it possible to distinguish a validation problem from a constraint violation, a stale update, a lock or concurrency issue, and a dependency failure.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. For a write, inspect the SQL and bound parameters, the affected-row count or RETURNING result, the transaction and constraint errors, and the relevant locks or query plan. A zero-row update may indicate a missing record, a stale version predicate, or an overly restrictive condition; it is not automatically success.

Interview questions

What problem does INSERT solve, and what trade-off or failure mode would make you choose a different approach?
What problem does UPDATE solve, and what trade-off or failure mode would make you choose a different approach?
What problem does DELETE solve, and what trade-off or failure mode would make you choose a different approach?
What problem does RETURNING solve, and what trade-off or failure mode would make you choose a different approach?
What problem does UPSERT solve, and what trade-off or failure mode would make you choose a different approach?

Answer these in terms of invariants and observable behavior, not just definitions. A strong answer can name the relevant constraint or predicate, describe the concurrency or failure case, and explain what the caller should receive.

Checkpoint

Without notes, explain INSERT, UPDATE, DELETE, UPSERT, RETURNING, and Safe Writes to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.