INSERT, UPDATE, DELETE, RETURNING, and UPSERT

PostgreSQL data manipulation uses INSERT, UPDATE, and DELETE; RETURNING exposes the rows actually changed, and ON CONFLICT resolves a declared uniqueness conflict atomically. Safe writes define the invariant, target exact rows, check the affected result, run inside an appropriate transaction, and remain correct when requests retry.

What will you be able to do?

• Insert one or many rows and capture generated values.
• Update or delete with precise predicates and evidence.
• Use ON CONFLICT without hiding unrelated errors.
• Design a retry-safe idempotency record.

Prerequisites and mental model

Know the foundation constraints. Treat every write as a set transformation: PostgreSQL may change zero, one, or many rows. Your intent must include the expected cardinality.

How do writes work?

Update with a tenant boundary and current-state precondition:

$1 and $2 are server-side parameters in application APIs. Zero returned rows could mean not found, wrong tenant, or invalid current state; the application must choose a non-leaking response.

Delete dependents only when the product lifecycle permits it:

What is an upsert?

Use a declared conflict target:

This is atomic for that unique key. It does not make a whole workflow idempotent, and it can overwrite a newer value unless the update has a version/time predicate.

How do you make request retries safe?

Create an idempotency table whose unique key includes tenant and operation scope. In one transaction, claim the key, perform the business change, and store the stable response. A concurrent duplicate either sees the committed result or conflicts and retries according to a bounded policy.

Never use ON CONFLICT DO NOTHING as a blanket way to suppress errors. It can turn a missing expected write into apparent success.

Why does this matter in AI-native systems?

Model calls and tool workflows retry after timeouts, network failures, or uncertain responses. Without idempotency, “create ticket,” “send refund,” or “append memory” can happen twice. Persist tool-call identity, parameters hash, approval, state, and outcome under a unique constraint; the model’s conversational memory is not a transaction log.

Prove it worked

Inside a rollbackable transaction:

1. Insert a customer and capture its ID.
2. Repeat the upsert with a changed display name.
3. Prove there is still exactly one row for the tenant/email.
4. Run a conditional update once, then repeat it and prove the second returns zero rows.

Use GET DIAGNOSTICS inside procedural code or the application driver’s row count when RETURNING is not appropriate.

Production judgment

• Broad writes can lock many rows and generate WAL; batch with stable boundaries.
• Foreign-key cascades can amplify deletes; inspect dependencies and retention rules.
• Soft deletion complicates every unique key, query, index, and RLS policy; use it only for a stated lifecycle.
• UPSERT concurrency semantics deserve tests with two sessions.
• Audit facts should be append-only or tamper-evident according to risk.

Common mistakes

• All rows changed: missing predicate → inspect with the same WHERE, transact, and verify cardinality.
• Retry duplicates: no idempotency key → persist a scoped unique operation key.
• Lost update: unconditional overwrite → add version/current-state predicate or locking.
• Upsert hides stale data: conflict update always wins → compare version or modified time.

AI Pair-Programmer Prompt

Hands-on exercise

Add version integer NOT NULL DEFAULT 1 to tickets in a rolled-back experiment. Write an update that succeeds only for an expected version and increments it.

Acceptance criterion: the first update returns version 2; replaying version 1 returns zero rows without overwriting the newer state.

Knowledge check

1. What evidence does RETURNING provide?
2. Does UPSERT make a multi-step workflow idempotent?
3. Why include tenant scope in write predicates?
4. What does zero rows from a conditional update mean?

---

Answers

1. The values from rows actually inserted, updated, or deleted.
2. No; it atomically handles one declared uniqueness conflict.
3. It prevents cross-tenant access and makes the ownership condition explicit.
4. No row met all preconditions; the application interprets that without leaking data.

Completion checklist

• Writes use exact predicates and parameters.
• Expected cardinality is checked.
• Retry and conflict behavior is explicit.
• Destructive lifecycle rules are documented.

Primary references

• INSERT
• UPDATE
• DELETE
• Returning data