14 — INSERT, UPDATE, DELETE

-- Single row.
INSERT INTO customers (name, city) VALUES ('Alice', 'NYC');

-- Multi-row: one statement, multiple value tuples. Reduces parse + round-trip overhead.
-- PG sends one PARSE + N BINDs; the planner plans once, executes N times.
INSERT INTO customers (name, city) VALUES
  ('Bob', 'LA'),
  ('Carol', 'NYC'),
  ('Dave', 'Chicago');

-- All columns in declaration order (omit column list — fragile, avoid in app code).
-- If the table's column order changes, this INSERT breaks silently.
INSERT INTO customers VALUES (DEFAULT, 'Eve', 'Seattle');

-- Insert from a query (data migration, ETL, archiving).
INSERT INTO archive_orders
SELECT * FROM orders WHERE ordered_on < '2023-01-01';

-- Insert with default values for all columns.
INSERT INTO events DEFAULT VALUES;

-- For large loads (100K+ rows), COPY is 10-100x faster than INSERT.
-- COPY streams rows directly into the table, bypassing most per-row overhead.
COPY customers (name, city) FROM '/path/to/customers.csv' WITH (FORMAT csv, HEADER true);

-- COPY from stdin (application drivers support this):
COPY customers (name, city) FROM STDIN WITH (FORMAT csv);
-- Application sends rows terminated by \n, then \. to end.

-- For bulk INSERT from application code: batch 100-1000 rows per statement
-- to reduce network round-trips and parse overhead.

-- DO UPDATE: on conflict on the email unique constraint, update the name.
-- EXCLUDED is the pseudo-table of "the row that was proposed for insertion."
INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;       -- overwrite with the new value

-- DO NOTHING: ignore the duplicate (silent skip, no error).
INSERT INTO subscribers (email) VALUES ('a@x.com')
ON CONFLICT (email) DO NOTHING;

-- Conflict on a specific constraint by name (not column):
INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT ON CONSTRAINT users_email_key
DO UPDATE SET name = EXCLUDED.name;

-- Conflict on composite unique key:
INSERT INTO daily_stats (day, metric, value) VALUES ('2024-01-01', 'sales', 100)
ON CONFLICT (day, metric)
DO UPDATE SET value = EXCLUDED.value;

-- Conditional update: only update if the new value is different.
-- The WHERE clause on DO UPDATE acts as a guard — skip the update if it's false.
INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
WHERE users.name IS DISTINCT FROM EXCLUDED.name;
-- IS DISTINCT FROM handles NULL safely (NULL IS DISTINCT FROM NULL = false).

-- Overwrite: set name to the new value from the INSERT.
DO UPDATE SET name = EXCLUDED.name;

-- Increment: add the new value to the existing value.
DO UPDATE SET count = counters.value + EXCLUDED.value;

-- Conditional: only update if the new value is larger.
DO UPDATE SET value = EXCLUDED.value
WHERE EXCLUDED.value > stats.value;

-- Get the auto-generated id back without a second query.
-- RETURNING is atomic with the INSERT — no race condition, no currval() fragility.
INSERT INTO customers (name, city) VALUES ('Frank', 'Boston')
RETURNING id, name;

-- Multiple rows: returns one row per inserted tuple.
INSERT INTO orders (customer_id, amount) VALUES (1, 50), (1, 75)
RETURNING id, amount;

-- UPDATE returning the changed rows.
UPDATE orders SET status = 'shipped' WHERE status = 'pending'
RETURNING id, status;

-- DELETE returning what was removed (audit trail).
DELETE FROM sessions WHERE expires_at < now()
RETURNING id, user_id;

-- Upsert with RETURNING: get the id whether it was an insert or a conflict.
-- DO UPDATE is required (DO NOTHING returns nothing on conflict).
INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = users.name  -- no-op update
RETURNING id;
-- Returns the id of the existing row (if conflict) or the new row (if inserted).

-- Update specific rows.
UPDATE orders SET status = 'shipped' WHERE id = 42;

-- Update multiple columns.
UPDATE customers SET city = 'LA', name = 'Bob' WHERE id = 2;

-- Atomic read-modify-write: the expression is evaluated against the current row value.
-- Safe at any isolation level — no lost update because the DB reads + writes in one step.
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Update based on a join (PostgreSQL FROM clause).
-- The target table (orders) is NOT re-listed in FROM — it's already the target.
UPDATE orders o
SET status = c.status
FROM customers c
WHERE o.customer_id = c.id AND c.status = 'banned';

-- Update with a subquery (portable, standard SQL).
UPDATE orders
SET amount = (SELECT AVG(amount) FROM orders WHERE customer_id = 1)
WHERE id = 99;

-- ❌ This updates EVERY row in the table. Always include WHERE.
UPDATE orders SET status = 'shipped';  -- no WHERE → all rows updated

-- ✅ Safety check: run SELECT with the same WHERE first.
SELECT count(*) FROM orders WHERE status = 'pending' AND id = 42;
-- Confirm the count is what you expect, then:
UPDATE orders SET status = 'shipped' WHERE status = 'pending' AND id = 42;

-- PostgreSQL: UPDATE ... FROM (the target table is NOT in FROM).
UPDATE orders o
SET status = c.status
FROM customers c
WHERE o.customer_id = c.id AND c.status = 'banned';

-- MySQL: UPDATE ... JOIN.
UPDATE orders o JOIN customers c ON o.customer_id = c.id
SET o.status = c.status
WHERE c.status = 'banned';

-- Standard SQL (portable): correlated subquery.
UPDATE orders o
SET status = (SELECT c.status FROM customers c WHERE c.id = o.customer_id)
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'banned');

-- Delete specific rows.
DELETE FROM orders WHERE id = 42;

-- Delete all rows (slow — use TRUNCATE for whole-table).
-- Creates N dead tuples, fires triggers, WAL per row, bloats until VACUUM.
DELETE FROM orders;

-- Delete based on a join (PostgreSQL USING).
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.status = 'banned';

-- Delete returning what was removed.
DELETE FROM sessions WHERE expires_at < now() RETURNING id, user_id;

-- ⚠️ DELETE without WHERE empties the table — same footgun as UPDATE.

-- PostgreSQL USING: join-based delete, concise.
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.status = 'banned';

-- Standard SQL subquery (portable, but may be slower if planner can't optimize):
DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'banned');

-- ⚠️ If multiple `c` rows match one `o` row, `o` is still deleted ONCE (not multiple times).
--    USING is safe — no duplicate deletion.

-- Real-time stats aggregation: events arrive continuously, stats are upserted.
-- The same (day, metric) key may arrive hundreds of times per second.

CREATE TABLE daily_stats (
  day        DATE         NOT NULL,
  metric     TEXT         NOT NULL,
  count      BIGINT       NOT NULL DEFAULT 0,
  sum_value  NUMERIC(20,2) NOT NULL DEFAULT 0,
  max_value  NUMERIC(20,2),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
  PRIMARY KEY (day, metric)
);

-- Upsert: on conflict, increment count, add to sum, take max, update timestamp.
-- EXCLUDED.count is the proposed insert value (the batch's count for this key).
-- stats.count is the existing row's value.
-- IS DISTINCT FROM guards against pointless updates (reduces write amplification).
INSERT INTO daily_stats (day, metric, count, sum_value, max_value)
VALUES ('2024-01-01', 'sales', 1, 150.00, 150.00)
ON CONFLICT (day, metric)
DO UPDATE SET
  count      = daily_stats.count + EXCLUDED.count,
  sum_value  = daily_stats.sum_value + EXCLUDED.sum_value,
  max_value  = LEAST(daily_stats.max_value, EXCLUDED.max_value),
  updated_at = now()
WHERE EXCLUDED.count > 0;  -- skip no-op upserts

-- Join-based UPDATE for denormalized data sync:
-- Sync the customer's total_orders and total_amount into a denormalized cache.
UPDATE customer_stats cs
SET
  total_orders  = agg.order_count,
  total_amount  = agg.total_amount,
  updated_at    = now()
FROM (
  SELECT customer_id,
         count(*)   AS order_count,
         sum(amount) AS total_amount
  FROM orders
  WHERE ordered_on >= now() - interval '1 day'
  GROUP BY customer_id
) agg
WHERE cs.customer_id = agg.customer_id;
-- The subquery aggregates once; the UPDATE joins to it by customer_id.
-- Without the subquery (correlated subquery per row), this would be O(N*M).

-- Deleting millions of rows in one statement holds locks for a long time,
-- creates millions of dead tuples, and can block VACUUM.
-- Batch it: delete N rows per statement, repeat until no rows are deleted.

-- PostgreSQL doesn't support DELETE ... LIMIT (MySQL does).
-- Use the id IN (SELECT ... LIMIT n) pattern.

DO $$
DECLARE
  deleted_count INTEGER;
  batch_size    INTEGER := 10000;
BEGIN
  LOOP
    DELETE FROM logs
    WHERE created_at < '2023-01-01'
      AND id IN (
        SELECT id FROM logs
        WHERE created_at < '2023-01-01'
        ORDER BY id
        LIMIT batch_size
      );
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    EXIT WHEN deleted_count = 0;
    PERFORM pg_sleep(0.01);  -- brief pause to let other queries proceed
  END LOOP;
END $$;

-- For updates, use a CTE to select IDs to update, then update in batches:
WITH batch AS (
  SELECT id FROM orders
  WHERE status = 'pending' AND created_at < '2024-01-01'
  ORDER BY id
  LIMIT 10000
  FOR UPDATE SKIP LOCKED   -- skip rows locked by other workers
)
UPDATE orders SET status = 'expired'
WHERE id IN (SELECT id FROM batch);

-- ❌ WRONG: row-by-row updates from application code.
-- N separate queries = N round-trips, N parses, N plan executions.
-- For 100K rows: 100K queries, ~100 seconds of round-trip overhead alone.

-- Pseudo-code (application layer):
-- for row in rows:
--     db.execute("UPDATE products SET price = ? WHERE id = ?", new_price, row.id)

-- ✅ CORRECT: set-based UPDATE — one query, one plan, one scan.
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- 100K rows updated in one statement, ~1 second.

-- ✅ If the new values are heterogeneous (different price per row), use a VALUES join:
UPDATE products p
SET price = v.new_price
FROM (VALUES
  (1, 10.99),
  (2, 25.50),
  (3, 7.00)
) AS v(id, new_price)
WHERE p.id = v.id;
-- One statement, N rows — the planner can use an index on p.id for the join.

-- ❌ This updates every row in the table — the classic footgun.
UPDATE orders SET status = 'shipped';

-- ✅ Always include WHERE. Run a SELECT with the same predicate first.
SELECT count(*) FROM orders WHERE status = 'pending';
-- Confirm the count, then:
UPDATE orders SET status = 'shipped' WHERE status = 'pending';

-- Some tools (mysql --safe-updates, psql with \set) block WHERE-less DML.

INSERT INTO counters (key, value) VALUES ('visits', 1)
ON CONFLICT (key) DO UPDATE SET value = 1;