Modifying data: INSERT, UPDATE, DELETE, and DDL

Everything so far has been the read side of SQL: SELECT and its friends. But data has to get INTO the database, and it has to be corrected, updated, and sometimes removed. That is the write side — DML (Data Manipulation Language: INSERT, UPDATE, DELETE) and DDL (Data Definition Language: CREATE, ALTER, DROP).

The write side is where SQL gets serious. A wrong SELECT wastes a minute. A wrong UPDATE or DELETE changes real data — a SACCO member's loan balance, a payment record, a member's phone number. This module teaches the writes and, just as importantly, the discipline that keeps them from becoming disasters.

INSERT: putting rows in

The basic form names the columns you are filling and gives one row of values.

INSERT INTO members (national_id, name, phone, branch_id, joined_date)
VALUES ('12345678', 'Amina Otieno', '+254712345678', 3, '2026-07-29');

Always list the columns explicitly. INSERT INTO members VALUES (...) without a column list depends on the physical column order, which silently breaks the day someone adds a column. Naming the columns is the same discipline as naming columns in SELECT instead of SELECT *.

Multi-row INSERT

You can insert many rows in one statement — faster and atomic (all rows land, or none do).

INSERT INTO loan_products (name, max_amount, max_tenor_months, interest_rate, requires_guarantor)
VALUES
  ('Salary Advance',    100000,  6, 0.05, false),
  ('School Fees Loan',  300000, 12, 0.09, true),
  ('Business Loan',    1000000, 36, 0.13, true);

INSERT ... SELECT

You can feed an INSERT from a query instead of literal VALUES. This is how you copy or archive rows. Here we snapshot every fully repaid loan into a history table:

INSERT INTO loans_archive (loan_id, member_id, principal, disbursement_date, closed_date)
SELECT id, member_id, principal, disbursement_date, NOW()
FROM loans
WHERE status = 'completed';

RETURNING: get back what you just wrote

Postgres lets INSERT hand you columns from the rows it created — most usefully the auto-generated id, so you do not need a second query to find out what id the database assigned.

INSERT INTO loan_applications (member_id, product_id, requested_amount, application_date, status)
VALUES (42, 2, 250000, NOW(), 'pending')
RETURNING id, application_date;

UPDATE: changing rows in place

UPDATE sets new column values on the rows that match the WHERE clause.

UPDATE members
SET phone = '+254799999999'
WHERE id = 42;

You can set several columns at once, and the new value can be an expression referring to the current value. Here we apply a monthly interest charge to every active loan's outstanding balance:

UPDATE loans
SET outstanding_balance = outstanding_balance * 1.013,
    last_interest_applied = CURRENT_DATE
WHERE status = 'active';

UPDATE ... FROM: update using another table

Sometimes the new value comes from a related table. Postgres supports UPDATE ... FROM (a join in an update). Here we recompute each loan's outstanding balance from the sum of its repayments:

UPDATE loans l
SET outstanding_balance = l.principal - paid.total_paid
FROM (
  SELECT loan_id, SUM(principal_portion) AS total_paid
  FROM repayments
  GROUP BY loan_id
) paid
WHERE paid.loan_id = l.id;

The FROM clause names the source (here a subquery of repayment totals), and the WHERE clause joins it to the rows being updated. Only loans with matching repayment rows are touched.

DELETE vs TRUNCATE

DELETE removes the rows that match a WHERE clause. Like UPDATE, forgetting WHERE removes everything.

DELETE FROM loan_applications
WHERE status = 'rejected'
  AND decision_date < NOW() - INTERVAL '2 years';

TRUNCATE empties an entire table in one fast operation. It does not scan or log individual rows, cannot carry a WHERE clause, and is not easily rolled back on all systems. Use it only when you genuinely want to wipe a whole table (a staging table you reload nightly, say).

• DELETE ... WHERE — removes selected rows; slower; fully transactional; fires row triggers; can be rolled back
• DELETE with no WHERE — removes every row, one at a time
• TRUNCATE table — removes every row in bulk; very fast; resets sequences with RESTART IDENTITY; skips per-row triggers; meant for whole-table wipes

UPSERT: INSERT ... ON CONFLICT DO UPDATE

A common need: insert a row, but if one with the same key already exists, update it instead of erroring. This is an upsert (update-or-insert). Postgres spells it ON CONFLICT.

INSERT INTO member_balances (member_id, savings_balance, updated_at)
VALUES (42, 15000, NOW())
ON CONFLICT (member_id)
DO UPDATE SET
  savings_balance = EXCLUDED.savings_balance,
  updated_at      = EXCLUDED.updated_at;

If no row for member 42 exists, this inserts one. If one does exist (a conflict on the member_id unique key), it runs the DO UPDATE instead. EXCLUDED is a special alias for the row you tried to insert, so EXCLUDED.savings_balance is the 15000 you supplied. ON CONFLICT ... DO NOTHING is the other variant: insert if new, silently skip if it already exists.

DDL: defining the tables themselves

DDL creates and alters the structure that DML fills. You read schemas constantly; sometimes you also create them. Here is a CREATE TABLE that uses every constraint worth knowing:

CREATE TABLE loans (
  id                 BIGSERIAL PRIMARY KEY,
  member_id          BIGINT      NOT NULL REFERENCES members(id),
  product_id         BIGINT      NOT NULL REFERENCES loan_products(id),
  principal          NUMERIC(14,2) NOT NULL CHECK (principal > 0),
  interest_rate      NUMERIC(5,4)  NOT NULL,
  tenor_months       INTEGER     NOT NULL CHECK (tenor_months BETWEEN 1 AND 60),
  outstanding_balance NUMERIC(14,2) NOT NULL DEFAULT 0,
  status             TEXT        NOT NULL DEFAULT 'pending'
                       CHECK (status IN ('pending','active','completed','defaulted')),
  disbursement_date  DATE,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

• BIGSERIAL PRIMARY KEY — an auto-incrementing integer id that uniquely identifies each row
• NUMERIC(14,2) — exact decimal for money: 14 total digits, 2 after the point. Never store money in FLOAT — rounding errors are real cash
• NOT NULL — the column must always have a value; the insert fails otherwise
• REFERENCES members(id) — a FOREIGN KEY: every member_id must exist in members, so you cannot create a loan for a member who does not exist
• DEFAULT 0 / DEFAULT NOW() — the value used when the INSERT does not supply one
• CHECK (...) — a rule the row must satisfy: a positive principal, a sane tenor, a status from a known set

ALTER TABLE

Schemas evolve. ALTER TABLE changes an existing table without recreating it — add a column, add a constraint, change a default.

ALTER TABLE members ADD COLUMN kyc_verified BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE loans ADD CONSTRAINT chk_balance_nonneg CHECK (outstanding_balance >= 0);
ALTER TABLE members ALTER COLUMN phone DROP NOT NULL;

DROP

DROP removes an object entirely — a table, and all its data, gone. There is no WHERE and no partial version.

DROP TABLE loans_staging;
-- IF EXISTS avoids an error when the table may already be gone:
DROP TABLE IF EXISTS loans_staging;

Transactions: why writes belong in BEGIN/COMMIT

A transaction groups several statements so they succeed or fail as one unit. Consider a loan repayment: you reduce the loan's outstanding balance AND insert a repayment record. If the first succeeds and the second fails, the books are now wrong — money vanished. A transaction makes both happen or neither.

BEGIN;

UPDATE loans
SET outstanding_balance = outstanding_balance - 5000
WHERE id = 77;

INSERT INTO repayments (loan_id, paid_date, amount, principal_portion, interest_portion)
VALUES (77, CURRENT_DATE, 5000, 4500, 500);

COMMIT;

Nothing is permanent until COMMIT. If either statement errors, or you realise the loan id was wrong, ROLLBACK undoes everything back to the BEGIN as if it never happened:

BEGIN;

UPDATE loans SET outstanding_balance = 0 WHERE status = 'active';  -- oops, no id!

SELECT id, outstanding_balance FROM loans WHERE status = 'active';  -- inspect the damage

ROLLBACK;  -- undo it all, nothing was saved

The write-side safety drill

Adopt this as muscle memory for every UPDATE and DELETE against real data:

1. Write the WHERE clause as a SELECT first: SELECT * FROM loans WHERE id = 77; — confirm it returns exactly the rows you intend, and count them
2. Wrap the change in BEGIN; ... so nothing is committed yet
3. Run the UPDATE / DELETE and check the reported row count against what your SELECT returned
4. Optionally use RETURNING * to see the actual changed rows
5. COMMIT only when it all looks right — otherwise ROLLBACK and start over