Updates, upserts and bulk imports

Conditional updates and deletes

Suppose the shop wants to raise P7 from 20.00 to 22.00, but only if nobody has already changed that price. The key identifies the product. The expected price expresses the condition under which this particular change is allowed.

UPDATE products
SET price = 22.00
WHERE product_id = 'P7' AND price = 20.00
RETURNING product_id, price;

SET says which values to assign. WHERE decides which rows are eligible. Read the two conditions together: “the product is P7, and its price is 20.00”. Compare a starting price of 20.00 with one that has already changed to 22.00.

With P7 at 20.00, the statement returns P7 at 22.00. With P7 already at 22.00, it returns no row. The statement with only WHERE product_id = 'P7' would assign 22.00 whatever price P7 had. Without a WHERE clause it would assign 22.00 to every product, including the tea and the teapot.

An earlier SELECT can preview the intended rows. It cannot make a later write conditional: the write must contain the condition too. The expected-price test checks the value at the write; it does not prove that the row has never changed and changed back. A version number can express a stricter “this revision only” rule when that is needed.

Assigning a value or changing a value

SET price = 22.00 assigns a value. An expression such as SET stock = stock + 2 calculates a new value from the stored row. Starting at stock 1, that expression gives 3; running it again gives 5. Calculating 3 in application code after a read and later sending SET stock = 3 expresses a different operation.

This distinction matters for replay as well as concurrency. It does not, by itself, solve a purchase involving stock, an order and several lines. Transactions and isolation owns the rules for keeping those changes consistent when buyers act together.

Constraints still apply

Passing WHERE makes a row eligible. It does not suspend the schema’s rules. Setting P7’s price to −4.50 fails the shop’s non-negative price constraint. Deleting P7 fails its foreign-key check while order lines still refer to it.

DELETE FROM products
WHERE product_id = 'P7'
RETURNING product_id;

DELETE uses its condition in the same way as UPDATE, but removes the whole eligible row. In this schema the attempt raises an error and P7 remains. There is no cascading deletion declared on the order-line reference. Choosing a different reference action would change the result, so check the declared relationship before deleting.

A condition that admits no row is a successful statement with no change. A constraint violation is a failed statement. Those are different outcomes, and the caller should treat them differently. Nor does one successful row condition establish a rule about several rows: “this product has an acceptable price” is a smaller claim than “all payments together stay below the order total”.

What the affected rows tell you

PostgreSQL reports an affected-row count. RETURNING also gives values from the rows the statement changed, saving a separate lookup. The guarded update returned the new catalogue price, so the caller knows which row the statement accepted.

An update count is not necessarily a count of different values. After the first change, UPDATE products SET price = 22.00 WHERE product_id = 'P7' still reports one updated row even though P7 already costs 22.00. If “already equal” needs its own outcome, compare the relevant old and proposed fields explicitly; PostgreSQL’s IS DISTINCT FROM comparison also handles NULL values.

The counts here assume the sample schema, with no triggers suppressing updates. On a real table, triggers can affect which changes take place and what is returned. A later diagnostic read may help explain zero rows, but it is a new observation, not a record of what the earlier statement saw.

Returned rows also do not establish that a surrounding transaction committed. BEGIN starts a transaction; COMMIT accepts its work together; ROLLBACK abandons it. An update can return P7 at 22.00 and a later rollback leave its committed price at 20.00. With no explicit transaction, PostgreSQL normally runs each statement in its own transaction; client libraries may manage boundaries for you.

Choosing an upsert’s meaning

An ordinary insert of P7 fails because that primary key already exists. An upsert means “insert, or update an existing row on a specified key conflict”. PostgreSQL spells it INSERT … ON CONFLICT … DO UPDATE.

The catalogue feed proposes two rows. P7 exists, so only its name and price should change. P11 is new, so the shop creates it with stock 0.

INSERT INTO products (product_id, name, price, stock)
VALUES ('P7', 'Blue mug', 22.00, 0),
       ('P11', 'Tea strainer', 8.00, 0)
ON CONFLICT (product_id) DO UPDATE
SET name = EXCLUDED.name,
    price = EXCLUDED.price
RETURNING product_id, name, price, stock;

EXCLUDED names the row proposed for insertion. For P7, EXCLUDED.price is 22.00. The update assigns that price and the supplied name, and leaves stock alone because stock is absent from its SET list. The proposed stock 0 is used for a new product such as P11; it does not reset P7’s existing stock.

The mug’s agreed unit price on order 103 remains 20.00. That price is a different fact, stored on the order line. Updating products.price neither changes it nor authorises a second update to reprice the order. The distinction is developed in Relational data modelling.

Ignore, replace or accumulate

A duplicate key needs a business decision before it needs syntax. The right action depends on what the source’s row means.

DO NOTHING does not check that two payloads agree. If P7 already costs 20.00, ignoring a proposal for 22.00 leaves it at 20.00. That may be intentional, but it is not confirmation that the proposal was previously applied.

An assignment and a delta make different promises when the same source is applied again.

Repeating an assignment of 22.00 has the same final price as applying it once. Repeating a delta adds it twice. Even replacement can be wrong later: replaying an old file can overwrite a newer catalogue price. Safe replay therefore needs a rule about source identity and ordering as well as an upsert.

ON CONFLICT (product_id) handles the specified unique-key conflict. It does not excuse a negative price, a missing required value or a broken foreign key. Without an additional update condition, PostgreSQL’s upsert provides an atomic insert-or-update decision for that key even when another writer inserts it concurrently, provided no independent error occurs.

One proposed value for each key

The destination key and the source-row identity answer different questions. B17’s row 1 identifies one received record; P7 identifies the product it would change. Two distinct source rows can propose conflicting values for the same destination.

This importer rejects both proposals before changing products. Here the two proposals are 22.00 and 23.00. Another source might supply a revision number that identifies an authoritative winner. Choose and record that rule; position in a file is not automatically a last-writer policy.

SELECT product_id_raw, count(*)
FROM catalogue_raw
WHERE batch_id = 'B17'
GROUP BY product_id_raw
HAVING count(*) > 1;

For ordinary B17, this returns no rows. In the duplicate variant it identifies P7. PostgreSQL refuses a multi-row ON CONFLICT DO UPDATE that would affect one existing row twice. UPDATE … FROM has a different trap: if several source rows join to one target row, it uses one match without a predictable choice. Neither behaviour implements a conflict-resolution policy. Make the validated source unique on its destination key first.

Load into staging first

A staging table holds received data before it becomes shop data. It lets the importer preserve a bad proposal, explain its rejection and apply the good proposals deliberately. Loading straight into products mixes transport, validation and catalogue changes into one operation.

Batch B17 has four immutable source rows. Keep the original file, its identity and checksum, the parser’s settings, and each record’s source row number. A CSV record can span physical lines, so define what that row number counts. Here it counts data records, starting at 1.

(batch_id, row_no) identifies a received record, including a rejected one. Store the original field values as text and keep the interpreted values and validation decision separately. Fixing row 3 must not erase the evidence that B17 originally contained −4.50.

Validate the proposed rows

Validation has several layers. A file may be impossible to parse at all; a parsed record can still contain an invalid number; a valid number can break a shop rule. Keep those outcomes distinct.

Decide how to handle rounding before converting prices. This feed accepts at most two decimal places; it rejects an extra fraction rather than silently rounding it. The worked SQL checks that format and conversion before casting, then records a reason for each rejected row. A cast applied indiscriminately to untrusted text can stop validation at its first bad value.

Validation produces three pending proposals and one retained rejection. Final table constraints still apply when proposals are written. In particular, an earlier cross-table check cannot promise that a referenced row will remain available until application.

If applying B17 stops and restarts, each source row needs a durable outcome so the resumed import can select only the remaining work.

Account for every source row

“Four rows loaded” means that staging received four records. It does not mean that four catalogue changes happened. Before application, B17 has three pending rows and one rejected row. Every received identity must have exactly one validation decision; any unvalidated row means the importer is not ready to apply the batch.

This importer counts a row as applied when its accepted proposal has been written and its progress record committed. Assigning already-equal fields still counts as applied. There is no separate “unchanged” state here. An importer that deliberately skips equal proposals should record that decision and include it in reconciliation; silently filtering them out loses their outcome.

Apply a validated batch

Once input and decisions are sealed, select a group of pending proposals and apply them as a set. “Sealed” means their contents and identities are fixed: a later resume uses the same records, not a replacement file under the same batch ID. The importer must enforce that boundary through its write permissions or import procedure.

The example uses catalogue_decisions for typed proposals and their rejection reasons, and catalogue_applied for committed progress. A NULL reason means validation accepted the proposal. A unique index permits only one accepted product key per batch. The download keeps only the product and order-line columns needed for this example; the two order-103 lines retain their agreed total of 29.00. The full setup and validation are in the worked SQL download.

The following transaction takes at most two pending rows in source-row order. For the first chunk, those are rows 1 and 2: P7 and P11.

BEGIN;
CREATE TEMP TABLE chunk ON COMMIT DROP AS
SELECT d.batch_id, d.row_no, d.product_id, d.name, d.price
FROM catalogue_decisions d
WHERE d.batch_id = 'B17' AND d.reason IS NULL
  AND NOT EXISTS (
    SELECT 1 FROM catalogue_applied a
    WHERE (a.batch_id, a.row_no) = (d.batch_id, d.row_no)
  )
ORDER BY d.row_no
LIMIT 2;

WITH written AS (
  INSERT INTO products (product_id, name, price, stock)
  SELECT product_id, name, price, 0 FROM chunk
  ON CONFLICT (product_id) DO UPDATE
  SET name = EXCLUDED.name, price = EXCLUDED.price
  RETURNING product_id
)
INSERT INTO catalogue_applied (batch_id, row_no)
SELECT c.batch_id, c.row_no
FROM chunk c JOIN written w USING (product_id);
COMMIT;

The temporary chunk fixes which proposals this transaction is handling. INSERT … SELECT sends those rows to the same upsert used earlier. The written expression returns the product keys accepted by that write; joining them back to the chunk records their source identities as applied. It does not infer completion from an earlier preview.

The final COMMIT accepts the catalogue changes and applied records together. If the statement fails, roll back the transaction. A failure while recording progress must also roll back its catalogue writes. Otherwise a resume could find a changed product with no record that its source row had been applied.

This worked importer has one worker for B17, fixed decisions, ordinary tables and no other writer of these catalogue fields during application. Its chunk selection is not a protocol for competing import workers or overlapping feeds. Existing stock can change independently because the upsert does not assign it. Coordinating concurrent invariants belongs in Transactions and isolation.

Upsert or MERGE

ON CONFLICT fits B17 because every accepted proposal either creates a product or replaces its catalogue fields on one unique-key conflict. MERGE is useful when a source-to-target match needs several explicit actions, such as updating matched rows, inserting unmatched source rows and treating unmatched target rows separately.

They are not interchangeable spellings. MERGE first matches source and target and chooses actions. At PostgreSQL’s Read Committed level, another transaction inserting the same key can cause its insert action to fail with a uniqueness error; it does not gain the upsert’s guarantee of an insert-or-update outcome. Choose it for the matching rules you need, with their concurrency behaviour understood.

B17 is a partial feed. A product missing from it is not a deletion request. A complete replacement catalogue would need a declared completeness boundary and an explicit policy for absent products, including their order-line references. Adding a “delete unmatched targets” action to this feed would invent authority it does not have.

Choose the commit boundary

One transaction can apply all three valid rows atomically: either all its catalogue changes and progress records commit, or none do. That is the simplest boundary when the batch is small enough and readers must not see part of it.

Committed chunks make a different promise. After the first chunk, readers can see P7 at 22.00 and P11 at 8.00 while P9 still costs 35.00. A later rollback cannot undo that earlier commit. Chunking bounds each transaction’s work and makes progress resumable, but deliberately permits a partly applied batch. The shop must accept that intermediate catalogue state.

A chunk is not a fixed number of bytes or a universal row count. Wider rows, checks and indexes change its cost. Measure a suitable size for the workload, balancing transaction cost, lock duration and the amount of partial progress readers can see. A requirement to publish a whole new catalogue at once may need a different design rather than smaller chunks.

Resume and reconcile an interrupted import

Suppose work stops after the P7/P11 chunk commits and before the P9 chunk starts. The durable records say exactly what remains: rows 1 and 2 applied, row 3 rejected, row 4 pending. Restarting the process does not reverse the first commit.

Run the same chunk transaction again against the sealed B17. It skips the recorded applied identities and selects row 4. P9 becomes 36.00 and row 4’s applied record commits with it. Run it once more and the selected chunk is empty.

Read status from the received rows, so missing decisions cannot disappear from the report:

SELECT r.row_no, r.product_id_raw,
  CASE WHEN d.row_no IS NULL THEN 'unvalidated'
       WHEN d.reason IS NOT NULL THEN 'rejected'
       WHEN a.row_no IS NOT NULL THEN 'applied'
       ELSE 'pending' END AS status,
  d.reason
FROM catalogue_raw r
LEFT JOIN catalogue_decisions d USING (batch_id, row_no)
LEFT JOIN catalogue_applied a USING (batch_id, row_no)
WHERE r.batch_id = 'B17'
ORDER BY r.row_no;

After the resume, rows 1, 2 and 4 are applied; row 3 is still rejected for a negative price. Thus four received records equal three applied plus one rejected, with no pending or unvalidated records. Check the identities as well as that arithmetic: a duplicated record and a missing record can cancel in a count.

Compare the intended catalogue fields too. P7 is 22.00, P11 is 8.00 with stock 0, P9 is 36.00, and P3 remains 4.50. Existing stock remains unchanged. Those checks establish the intended result at reconciliation time; if authorised catalogue writers continue afterwards, later differences are not automatically an import failure.

Replaying the same batch

A resume reuses B17’s original identity, source and decisions. It does not validate a different payload under B17 or erase its rejection. Corrected P3 data belongs to a distinct revision, linked back to the rejected source if that history is useful.

The applied records stop completed B17 rows from being selected again. The upsert alone would not do that: a replacement would still write, a delta could accumulate twice, and an old replacement could overwrite newer work. Replaying an earlier batch after a later one also needs an ordering policy; a batch ledger is not a source revision policy.

This interruption was known to happen before the next chunk began. If a connection disappears during COMMIT, the caller may not know whether that chunk committed. Recovering one logical request, checking that its payload agrees and retrieving its recorded result needs the fuller protocol in Retries and uncertain commit outcomes. The progress tables here demonstrate atomic import bookkeeping; they do not replace that protocol.

Choosing a bulk loading method

Receiving a batch and applying it are separate choices. B17 is small enough to receive with a multi-row insert. A large file usually deserves a bulk transport into staging, followed by the same validation and set-based application.

SQL COPY … FROM '/path/file.csv' reads a file on the database server, using the server’s filesystem access. COPY … FROM STDIN receives data over the client connection. The psql command \copy reads a file on the client and sends it through that connection. Choose according to where the file actually exists and which process may read it; a path on your laptop is not automatically a path on the server.

By default, PostgreSQL stops a COPY at an error. PostgreSQL 18’s ON_ERROR ignore can skip input type-conversion failures in text and CSV input, with an optional REJECT_LIMIT. It does not provide a general “skip bad business rows” mode: a negative numeric price can convert successfully and then fail a check constraint. It does not replace a retained rejection record either.

Loading raw text into staging preserves values that typed loading would reject. Malformed file structure can still prevent receipt, so retain the original file even then. Reconcile the sealed source, validation decisions and committed outcomes independently of the loader’s count.

The worked SQL file creates its own writes_example schema in a disposable database and uses literal inserts to receive B17. It includes validation, the first commit, the status report at the known interruption, resume, an empty replay and final reconciliation queries. It has been run as SQL in PGlite 0.5.8 (PostgreSQL 18.3); it is not a measured COPY or concurrent-server experiment. Use a fresh disposable database; the setup refuses to reuse an existing schema of that name.

Bulk transport saves overhead; it does not change what a row means. Index and storage costs belong with their own readings. A live schema backfill adds another problem—converging while callers keep writing—which is owned by Live schema changes and backfills.