Inserts Updates Upserts and MERGE

Overview

• Writes: INSERT, UPDATE, DELETE, MERGE, COPY
• Postgres Superpowers: RETURNING on every write, ON CONFLICT (UPSERT), UPDATE … FROM, DELETE … USING, data-modifying CTEs, MERGE (PG 15+, RETURNING on MERGE in PG 17+)
• MVCC Reminder: An UPDATE writes a new row version and marks the old one dead; DELETE marks it dead — VACUUM cleans up later
• Sample Data: E-commerce Practice Queries — all examples run inside BEGIN … ROLLBACK so the data stays unchanged

INSERT

Forms

BEGIN;
 
-- Single row
INSERT INTO users (email, full_name, country)
VALUES ('ivan@example.com', 'Ivan Petrov', 'RS');
 
-- Multi-row (one round trip, much faster than many single inserts)
INSERT INTO categories (name, parent_id) VALUES
  ('Tablets', 1),
  ('Garden',  NULL);
 
-- From a query
CREATE TEMP TABLE vip_users AS SELECT id, email FROM users WHERE false;   -- empty copy
INSERT INTO vip_users (id, email)
SELECT u.id, u.email
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'delivered');
 
-- All defaults
CREATE TEMP TABLE audit_ping (id int GENERATED ALWAYS AS IDENTITY, at timestamptz DEFAULT now());
INSERT INTO audit_ping DEFAULT VALUES;
 
SELECT count(*) AS vip_count FROM vip_users;
ROLLBACK;

RETURNING

• What: Return columns of the inserted/updated/deleted rows — no second SELECT round trip
• Typical Use: Get generated IDs, defaults, computed values
• Works On: INSERT, UPDATE, DELETE, and MERGE (PG 17+)

BEGIN;
INSERT INTO orders (user_id) VALUES (7)
RETURNING id, status, created_at;                -- status shows the DEFAULT 'pending'
ROLLBACK;

UPSERT: INSERT … ON CONFLICT

Syntax

INSERT INTO t (cols) VALUES (…)
ON CONFLICT (conflict_target)          -- unique columns / ON CONSTRAINT name / partial index predicate
DO NOTHING                             -- skip the row
| DO UPDATE SET col = EXCLUDED.col     -- EXCLUDED = the row you tried to insert
        [WHERE condition]              -- only update when this holds

• Requires: A unique index or constraint matching the conflict target
• Atomic: Safe under concurrency — no “check then insert” race
• EXCLUDED: Pseudo-table holding the proposed row; the existing row is referenced by the table name
• Limitation: The same key can’t appear twice in one INSERT … ON CONFLICT DO UPDATE statement (error: “command cannot affect row a second time”)

BEGIN;
 
-- DO NOTHING: idempotent insert (e.g. replaying events)
INSERT INTO users (email, full_name) VALUES ('asha@example.com', 'Asha Duplicate')
ON CONFLICT (email) DO NOTHING
RETURNING id;                                    -- returns 0 rows: nothing inserted
 
-- DO UPDATE: set price from a feed, only if it actually changed
INSERT INTO products (sku, name, category_id, price)
VALUES ('LAP-001', 'Air Laptop 13', 3, 949.00),
       ('LAP-003', 'Air Laptop 15', 3, 1299.00)
ON CONFLICT (sku) DO UPDATE
   SET price = EXCLUDED.price
 WHERE products.price IS DISTINCT FROM EXCLUDED.price
RETURNING sku, price, (xmax = 0) AS inserted;
 
-- Counter pattern: increment or create
CREATE TEMP TABLE page_views (path text PRIMARY KEY, views bigint NOT NULL);
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + 1;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + 1;
SELECT * FROM page_views;                        -- /home | 2
 
ROLLBACK;

Approach	Race-Safe	Notes
SELECT then INSERT/UPDATE in app	No	Two requests both see “missing” → duplicate error
INSERT … ON CONFLICT	Yes	Postgres-native; needs unique index
MERGE	Not fully	Can still raise unique violations under concurrent inserts; use ON CONFLICT for pure upserts

UPDATE

Basic and Computed

BEGIN;
UPDATE products
SET price = round(price * 0.9, 2),
    stock = stock + 10
WHERE category_id = 8
RETURNING sku, price, stock;
ROLLBACK;

UPDATE … FROM (Join in an Update)

• What: Update rows using values from other tables
• Gotcha: If the FROM produces several matches for one target row, Postgres picks one arbitrarily — aggregate first

BEGIN;
-- Mark orders as 'paid' when they have a successful payment covering the total
UPDATE orders o
SET    status = 'paid'
FROM  (SELECT order_id, sum(amount) AS paid FROM payments WHERE status = 'succeeded' GROUP BY order_id) p,
      (SELECT order_id, sum(quantity * unit_price) AS due FROM order_items GROUP BY order_id) d
WHERE  p.order_id = o.id
  AND  d.order_id = o.id
  AND  o.status = 'pending'
  AND  p.paid >= d.due
RETURNING o.id, o.status;                        -- 0 rows: pending orders are unpaid in the seed
ROLLBACK;

BEGIN;
-- Recalculate stock from a delivery batch
CREATE TEMP TABLE delivery (sku text, qty int);
INSERT INTO delivery VALUES ('ACC-003', 25), ('LAP-002', 3);
 
UPDATE products p
SET    stock = p.stock + d.qty
FROM   delivery d
WHERE  d.sku = p.sku
RETURNING p.sku, p.stock;
ROLLBACK;

UPDATE with a Subquery (Portable)

BEGIN;
UPDATE users u
SET country = 'XX'
WHERE country IS NULL
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
RETURNING id, full_name, country;
ROLLBACK;

DELETE

DELETE … USING (Join in a Delete)

BEGIN;
-- Delete failed payments for delivered orders
DELETE FROM payments p
USING orders o
WHERE o.id = p.order_id
  AND o.status = 'delivered'
  AND p.status = 'failed'
RETURNING p.id, p.order_id, p.amount;
ROLLBACK;

Batch Deletes on Big Tables

• Problem: One huge DELETE holds locks long, bloats the table, and generates a WAL spike
• Fix: Delete in batches by primary key, commit between batches; or partition by time and DROP/DETACH PARTITION (see Database Partition)

BEGIN;
-- One batch of at most 1000 rows; loop this from a script until 0 rows are affected
DELETE FROM payments
WHERE id IN (
    SELECT id FROM payments
    WHERE status = 'failed' AND paid_at < now() - interval '90 days'
    ORDER BY id
    LIMIT 1000
);
ROLLBACK;

MERGE (PG 15+)

Concept

• What: SQL-standard “synchronise target with source” — one statement with WHEN MATCHED / WHEN NOT MATCHED branches
• Actions: UPDATE, DELETE, INSERT, DO NOTHING
• PG 17+: WHEN NOT MATCHED BY SOURCE (target rows missing from source) and RETURNING with merge_action()
• vs ON CONFLICT: MERGE can delete and handle several conditions, but is not race-proof for inserts; ON CONFLICT is the safer pure upsert

BEGIN;
CREATE TEMP TABLE supplier_feed (sku text PRIMARY KEY, price numeric(10,2), stock int, discontinued boolean);
INSERT INTO supplier_feed VALUES
  ('ACC-001', 24.00, 300, false),   -- update
  ('ACC-003', 45.00,   0, true),    -- discontinued → deactivate
  ('ACC-009', 15.00, 500, false);   -- new product → insert
 
MERGE INTO products p
USING supplier_feed f ON f.sku = p.sku
WHEN MATCHED AND f.discontinued THEN
    UPDATE SET is_active = false, stock = 0
WHEN MATCHED THEN
    UPDATE SET price = f.price, stock = f.stock
WHEN NOT MATCHED AND NOT f.discontinued THEN
    INSERT (sku, name, category_id, price, stock)
    VALUES (f.sku, 'New item ' || f.sku, 4, f.price, f.stock)
RETURNING merge_action(), p.sku, p.price, p.stock, p.is_active;   -- PG 17+
 
ROLLBACK;

Data-Modifying CTEs

• What: INSERT/UPDATE/DELETE … RETURNING inside WITH, chained in one atomic statement
• Snapshot Rule: All sub-statements see the same snapshot — they can’t see each other’s changes except through RETURNING
• Classic Uses: Archive-then-delete, create parent + children, audit logging

BEGIN;
CREATE TEMP TABLE payments_archive (LIKE payments);
 
-- Move failed payments to an archive table in one statement
WITH moved AS (
    DELETE FROM payments
    WHERE status = 'failed'
    RETURNING *
)
INSERT INTO payments_archive
SELECT * FROM moved
RETURNING id, order_id, status;
 
ROLLBACK;

Bulk Loading with COPY

COPY vs INSERT

Method	Speed	Notes
Single-row INSERT	Slowest	One round trip + one commit per row if autocommit
Multi-row INSERT	Good	Batch 500–1000 rows per statement
COPY	Fastest	Streams rows, minimal parsing overhead
COPY into UNLOGGED/staging + INSERT … SELECT	Fastest for ETL	Validate/transform in SQL, then merge

-- Export a query to stdout as CSV (psql prints it)
COPY (SELECT sku, name, price FROM products WHERE category_id = 8 ORDER BY sku)
TO STDOUT WITH (FORMAT csv, HEADER);

# Server-side COPY reads files on the DB server (needs superuser / pg_read_server_files)
#   COPY products_staging FROM '/var/lib/postgresql/products.csv' WITH (FORMAT csv, HEADER);
# Client-side \copy (psql) reads a file on YOUR machine — usually what you want
psql -c "\copy products_staging FROM 'products.csv' WITH (FORMAT csv, HEADER)"

Fast Bulk Load Checklist

• Load into a Staging Table: Then INSERT … SELECT … ON CONFLICT into the real table
• Drop / Defer Indexes: Create indexes after loading big batches
• Large Transaction: One commit per batch, not per row
• ANALYZE Afterwards: So the planner knows the new data distribution
• COPY FREEZE: For a table created/truncated in the same transaction, avoids later vacuum work
• PG 17: COPY … ON_ERROR ignore skips bad rows instead of failing the whole load

Interview Questions

• “How do you do an upsert in Postgres?”: INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col
• “MERGE vs ON CONFLICT?”: MERGE handles insert/update/delete branches from a source; ON CONFLICT is the concurrency-safe upsert
• “Get the generated ID after insert?”: RETURNING id
• “Update using another table?”: UPDATE … FROM; make sure each target row matches at most one source row
• “Delete millions of rows safely?”: Batches by PK with commits, or drop a time partition
• “Fastest way to load 10M rows?”: COPY into a staging table, build indexes after, ANALYZE

Summary

• INSERT: Multi-row VALUES, INSERT … SELECT, RETURNING for generated values
• UPSERT: ON CONFLICT DO NOTHING / DO UPDATE with EXCLUDED, race-safe given a unique index
• UPDATE / DELETE: FROM / USING for joins; aggregate the source to avoid ambiguous matches
• MERGE: Standard multi-branch sync (PG 15+), RETURNING + NOT MATCHED BY SOURCE in PG 17
• Bulk: COPY / \copy, staging tables, batch deletes, ANALYZE after loads