Understanding INSERT, UPDATE, and DELETE Safely

INSERT, UPDATE, and DELETE change the data stored in a database.

These operations are essential, but an incorrect query can create duplicate records, change the wrong values, or remove important data.

Imagine a customers table containing 10,000 customers. You want to update one customer's email address.

Correct:
UPDATE customers
SET email = 'new@example.com'
WHERE customer_id = 105;

Dangerous:
UPDATE customers
SET email = 'new@example.com';

The second query has no WHERE clause, so it updates every row in the table.

The same risk applies to DELETE. Without a WHERE clause, DELETE removes every row from the target table, although the table structure remains.

INSERT has different risks. Repeating an INSERT may create duplicate records unless the database schema or application logic prevents them.

A safe workflow is:

1. Understand the intended change.
2. Validate the input.
3. Preview the target records when modifying or deleting existing data.
4. Execute the operation with appropriate safeguards.
5. Inspect the result and affected-row count.
6. Commit the transaction only after verification.
7. Record important operations in application logs when appropriate.

Important: A SELECT preview is useful, but it is not a guarantee that the subsequent mutation will affect exactly the same rows. Concurrent transactions or changing data can alter the outcome. Use suitable transactions, constraints, and locking strategies when correctness requires them.

The examples in this lesson use PostgreSQL and a simple customers table. You can run this setup in a test database.

The customer_id column is the primary key. The email column is required and unique, which prevents two customers from having the same non-NULL email address.

The CHECK constraint prevents negative account balances.

These constraints provide database-level protection even if application validation is accidentally bypassed.

Before changing existing records, run a SELECT query using the same filtering condition that you intend to use in UPDATE.

Suppose Ravi has changed his email address. First, find the exact customer:

SELECT customer_id, full_name, email
FROM customers
WHERE customer_id = 2;

If the result identifies the intended customer, execute the update.

UPDATE customers
SET email = 'ravi.new@example.com'
WHERE customer_id = 2
RETURNING customer_id, full_name, email;

RETURNING displays the values of the updated row. It helps you verify the result without issuing a separate SELECT.

Why use a primary key?

A primary key identifies a specific row. A condition such as WHERE full_name = 'Ravi Kumar' might match multiple records because names are not necessarily unique.

For bulk changes, preview the number of matching records as well:

SELECT COUNT(*)
FROM customers
WHERE balance < 300;

If you intend to update only one customer but the preview finds 50 rows, stop and investigate before proceeding.

Remember: previewing and updating in separate transactions can introduce a race condition. When concurrent changes matter, perform the operation within a suitable transaction and use appropriate locking or isolation.

DELETE removes rows from a table. Once committed, deleted data may not be recoverable through an ordinary SQL rollback.

Before deleting records, inspect the rows that match the intended condition.

SELECT customer_id, full_name, email
FROM customers
WHERE customer_id = 3;

If this returns the intended customer, you can delete that record:

DELETE FROM customers
WHERE customer_id = 3
RETURNING customer_id, full_name, email;

The returned values show which rows were deleted by the statement.

Avoid broad conditions such as:

DELETE FROM customers
WHERE created_at < CURRENT_TIMESTAMP;

This condition may match every existing customer whose creation time is in the past.

For cleanup jobs, define the intended cutoff explicitly, preview the matching rows, estimate the scope, and confirm that related records and retention requirements have been considered.

Foreign keys may also prevent a deletion when dependent records exist. If ON DELETE CASCADE is configured, deleting a parent row can automatically delete related child rows. Understand the schema before running the operation.

A transaction groups database operations into one unit of work.

PostgreSQL supports these main transaction commands:

BEGIN;
Starts a transaction.

COMMIT;
Makes the transaction's changes permanent.

ROLLBACK;
Discards the changes made in the current uncommitted transaction.

A transaction is useful when several changes must succeed or fail together.

Example: You want to increase a customer's balance and record a related adjustment in a second table. If either operation fails, you may need to undo both.

For a potentially risky operation, use a transaction to inspect the result before committing:

BEGIN;

UPDATE customers
SET balance = balance + 50
WHERE customer_id = 1
RETURNING customer_id, balance;

-- Inspect the result before deciding what to do.

ROLLBACK;

Because the transaction is rolled back, the balance change is discarded.

For an actual approved change, replace ROLLBACK with COMMIT after verifying the result.

Important limitations:
- ROLLBACK only reverses changes made within the current uncommitted transaction.
- If you already committed a transaction, a later ROLLBACK cannot undo it.
- Some client tools and application frameworks manage transactions automatically.
- External effects, such as an email already sent by an application, are not automatically reversed by a database rollback.
- Transactions do not replace backups or authorization controls.

PostgreSQL supports RETURNING with INSERT, UPDATE, and DELETE.

INSERT ... RETURNING
Shows values from newly inserted rows, including generated identity values.

UPDATE ... RETURNING
Shows the new values of updated rows.

DELETE ... RETURNING
Shows the values of rows removed by the statement.

This is useful for backend applications because the application can inspect the actual result of a mutation.

RETURNING is PostgreSQL-specific and is not supported with identical syntax by every database engine. Check the documentation for your target database.

A successful SQL statement does not always mean that the intended number of rows changed.

For example, an UPDATE can complete successfully while matching zero rows because the supplied customer_id does not exist.

An application should consider whether the number of affected rows is reasonable for the operation.

Examples:
- Updating one profile by primary key: zero rows may mean the record does not exist; more than one row should be impossible if the primary key is used correctly.
- Deleting a batch of expired sessions: several affected rows may be expected.
- Updating all product prices: a large number of affected rows may be intentional, but should be explicitly authorized and verified.

The database driver determines how affected rows are reported. PostgreSQL clients may expose command tags, row counts, or returned rows depending on the interface.

Do not assume that every driver reports counts identically. Also, an UPDATE that assigns a column its existing value may still count as an affected row in PostgreSQL, so a row count does not always mean that the stored value changed.

Example using a JavaScript PostgreSQL client:

const result = await client.query(
"UPDATE customers SET email = $1 WHERE customer_id = $2 RETURNING customer_id",
["ravi.new@example.com", 2]
);

if (result.rowCount !== 1) {
throw new Error("Expected to update exactly one customer");
}

The values are passed as parameters rather than concatenated into the SQL string. Parameterized queries help prevent SQL injection.

In production, ensure the surrounding transaction is rolled back when validation fails. A thrown application error by itself does not automatically roll back a transaction unless your transaction-management code handles it.

INSERT can fail or create unwanted data when inputs are invalid, required values are missing, or duplicate records are submitted.

Use these practices:

1. Specify column names rather than relying on the table's column order.
2. Validate user input in the application.
3. Use database constraints such as NOT NULL, UNIQUE, CHECK, and foreign keys.
4. Use parameterized queries in application code.
5. Handle duplicate requests deliberately.
6. Use transactions when an insert must be part of a larger atomic operation.

Example:

INSERT INTO customers (full_name, email, balance)
VALUES ('Aman Gupta', 'aman@example.com', 150.00)
RETURNING customer_id, full_name, email;

The UNIQUE constraint on email rejects a duplicate email address.

For a deliberate upsert in PostgreSQL, use ON CONFLICT with a clearly defined conflict target and intended action:

INSERT INTO customers (full_name, email, balance)
VALUES ('Aman Gupta', 'aman@example.com', 150.00)
ON CONFLICT (email)
DO NOTHING
RETURNING customer_id, email;

If a conflict occurs, DO NOTHING skips insertion and RETURNING returns no row for that skipped insert. This behavior should be handled explicitly by the application.

Do not use ON CONFLICT DO UPDATE automatically for every duplicate. Updating an existing record may overwrite data that should be preserved.

A transaction protects uncommitted database changes, but it does not protect against every production mistake.

Consider these safeguards:

Backups and recovery
- Maintain backups appropriate to the system's recovery requirements.
- Test restoration procedures instead of assuming backups are usable.
- For important systems, understand point-in-time recovery and the recovery objectives.

Database permissions
- Give application accounts only the privileges they need.
- Avoid using a database superuser for normal application requests.
- Restrict access to destructive operations when practical.

Validation
- Check business rules before modifying records.
- Use constraints to enforce rules at the database level.
- Reject unexpected affected-row counts.

Testing and deployment
- Test against representative data in a non-production environment.
- Review migrations and destructive scripts before deployment.
- Use a reviewed, explicit condition for bulk updates and deletes.
- Schedule large maintenance operations carefully.

Auditing
- Record who initiated sensitive changes, when they occurred, and the relevant business identifier.
- Avoid logging passwords, access tokens, or unnecessary personal information.

A LIMIT clause is not a universal safety mechanism for UPDATE or DELETE. PostgreSQL does not support a general LIMIT clause directly on these statements. For controlled batches, select target keys in a subquery or use an appropriate CTE pattern, and validate the batch carefully.

Never treat a database backup, a transaction, or a row limit as a replacement for all the other safeguards.

Large DELETE operations can consume substantial resources, generate significant write-ahead log activity, hold locks, and leave dead tuples that PostgreSQL later reclaims through vacuuming.

For a large cleanup, deleting a small, controlled batch at a time may reduce the size of each individual transaction. The correct batch size and schedule depend on workload, indexes, foreign keys, replication, and service requirements.

PostgreSQL supports a CTE-based batch pattern:

WITH batch AS (
SELECT customer_id
FROM customers
WHERE customer_id IN (...)
ORDER BY customer_id
LIMIT 100
)
DELETE FROM customers AS c
USING batch
WHERE c.customer_id = batch.customer_id
RETURNING c.customer_id;

The placeholder (...) is explanatory, not executable SQL. In a real cleanup, replace it with a carefully defined condition or use a suitable CTE that selects eligible rows.

For a more practical example, imagine a sessions table with an expires_at column. A batch can select expired session IDs and delete only those IDs. Ensure the query's filtering condition is correct and that the selected rows are protected against unexpected concurrent changes when necessary.

Do not run a batch cleanup until you have verified its predicate, dependencies, and expected scope. If the operation is part of a maintenance job, design retries and monitoring deliberately.

Mistake 1: Running UPDATE or DELETE without checking the condition.
Fix: Preview the target rows and verify the expected scope.

Mistake 2: Assuming a successful statement changed a row.
Fix: Inspect RETURNING results or the driver-reported affected-row count.

Mistake 3: Believing ROLLBACK can undo a committed transaction.
Fix: Understand transaction boundaries and plan recovery before committing.

Mistake 4: Assuming a SELECT preview guarantees the later mutation will affect identical rows.
Fix: Consider concurrency and use an appropriate transaction and locking strategy.

Mistake 5: Concatenating user input into SQL.
Fix: Use parameterized queries.

Mistake 6: Assuming LIMIT works directly on PostgreSQL UPDATE and DELETE.
Fix: Use a suitable CTE or subquery pattern for controlled batches.

Mistake 7: Assuming a backup is sufficient without testing restoration.
Fix: Regularly verify the recovery process.

Mistake 8: Ignoring foreign-key relationships.
Fix: Inspect dependencies and understand ON DELETE behavior before removing parent records.

Use the customers table from this lesson. Work in a test database.

Exercise 1:
Write a SELECT query to preview customer_id 1 before updating the customer's balance.

Exercise 2:
Update customer_id 1 by adding 25 to the balance and return the updated customer_id and balance.

Exercise 3:
Write a transaction that sets customer_id 1's balance to 0, inspects the result, and then rolls back the change.

Exercise 4:
Write a DELETE statement that removes customer_id 3 and returns the deleted customer's name.

Exercise 5:
Explain why checking the affected-row count matters when updating a customer by primary key.

Exercise 6:
An UPDATE query was committed and then the developer tried ROLLBACK. Explain why the rollback cannot undo the committed change.

Exercise 7:
Explain the difference between parameterized queries and string concatenation when building SQL in application code.

Answer these questions before continuing. Pay attention to transaction boundaries and the difference between a statement succeeding and affecting the intended rows.

Q1. Why should you preview rows before UPDATE or DELETE?
Answer: Previewing helps verify that the filtering condition targets the intended records and gives an estimate of the operation's scope.

Q2. What is the difference between COMMIT and ROLLBACK?
Answer: COMMIT makes the current transaction's changes permanent. ROLLBACK discards its uncommitted changes.

Q3. Can an UPDATE succeed while changing zero rows?
Answer: Yes. For example, the WHERE condition may match no rows. Application code should validate the result when a specific row is expected.

Q4. What does RETURNING provide in PostgreSQL?
Answer: It returns values from rows inserted, updated, or deleted by the statement.

Q5. Why are database constraints important?
Answer: Constraints enforce data rules at the database level, protecting integrity even when application validation is missing or incorrect.

Q6. Why are parameterized queries safer than string concatenation?
Answer: They keep supplied values separate from the SQL command structure, helping prevent SQL injection.

Q7. Why is a transaction not a substitute for a backup?
Answer: A transaction can reverse uncommitted changes, but it cannot ordinarily undo a committed mistake. Backups and recovery procedures address a different failure scenario.

Q8. How would you safely perform a large deletion?
Answer: Verify the eligibility condition, inspect dependencies and the expected row count, test the query, and consider controlled batches, transaction size, locking, monitoring, and recovery requirements.

Q9. What is a race condition between SELECT and UPDATE?
Answer: Another transaction may change the data after the preview but before the update. Appropriate transaction isolation, locking, or a carefully designed atomic statement can address this depending on the requirement.

Safe DML is about ensuring that the right records are changed, the result is verified, and unintended changes can be prevented or recovered from.

Remember these principles:

- INSERT adds records; validate inputs and handle duplicates deliberately.
- UPDATE changes existing records; verify the WHERE condition.
- DELETE removes records; inspect the target rows and check dependencies.
- BEGIN, COMMIT, and ROLLBACK control transaction boundaries.
- RETURNING helps inspect the rows affected by PostgreSQL DML.
- Affected-row counts help detect missing or unexpected matches.
- Constraints enforce important data rules.
- Parameterized queries help prevent SQL injection.
- Backups and tested recovery procedures remain necessary.
- Large cleanup jobs need careful batching, monitoring, and operational planning.

The key habit is simple: define the intended change, verify its scope, execute it with suitable safeguards, and confirm the outcome before treating the operation as complete.