Typical Mistakes When Working With Data

Working with a database is kinda like a programmer’s life: full of surprises. Even the most experienced dev can mess up—accidentally delete data, try to insert a duplicate, or break integrity constraints. But it’s not just about avoiding these mistakes, it’s also about knowing how to fix them if they happen. Let’s check out some of the most typical mistakes.

Mistake #1: Missing the WHERE Clause

The classic rookie mistake (and let’s be real, even pros do this sometimes) is forgetting to add a WHERE clause to an update or delete query. Queries without WHERE update or delete every row in the table.

-- Example of what NOT to do:
UPDATE students SET status = 'graduated';

-- Or like this:
DELETE FROM students;

What happens: imagine you run this query and suddenly your students table with all your student data is empty. Worst part? You can’t get the data back unless you have a backup or used transactions (and even then, it’s stressful).

How to avoid it: always add conditions to your UPDATE and DELETE queries so you know exactly which rows you’re changing or deleting.

-- The right way:
UPDATE students
SET status = 'graduated'
WHERE year_of_study = 4;

DELETE FROM students
WHERE status = 'expelled';

Another trick—before deleting, always run a SELECT to make sure your condition is set up right:

-- First, check:
SELECT * FROM students WHERE status = 'expelled';

-- Then, do the delete:
DELETE FROM students WHERE status = 'expelled';

Mistake #2: Breaking Data Uniqueness (UNIQUE)

If your table has a UNIQUE constraint, trying to insert a duplicate will throw an error.

-- Error because of duplicate email:
INSERT INTO students (name, email) VALUES ('Otto Lin', 'otto.lin@email.com');
INSERT INTO students (name, email) VALUES ('Peter Pen', 'otto.lin@email.com');

Error:

ERROR: duplicate key value violates unique constraint "students_email_key"

How to avoid it: before inserting, check if there’s already a row with the same values.

-- One way to do it:
SELECT * FROM students WHERE email = 'otto.lin@email.com';

-- Or use UPSERT:
INSERT INTO students (name, email)
VALUES ('Peter Pen', 'otto.lin@email.com')
ON CONFLICT (email) DO NOTHING;

Mistake #3: Breaking Integrity Constraints (FOREIGN KEY)

Let’s say you have two tables: students and enrollments, where student_id in enrollments is a foreign key referencing id in students. If you try to insert a row into enrollments with a student_id that doesn’t exist in students, you’ll get an error.

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 101); -- Error, because student_id 999 doesn’t exist

How to avoid it?

1. Always check if the record exists in the parent table before inserting into the related table:

SELECT * FROM students WHERE id = 999;

2. Use ON DELETE CASCADE so related records are automatically deleted when the parent record is deleted (but use with caution).

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(id) ON DELETE CASCADE,
    course_id INT
);

Mistake #4: Wrong Data Types

When inserting or updating data, PostgreSQL strictly checks data type compatibility. If you try to insert a string into a numeric field, you’ll get an error.

-- Error because of type mismatch:
INSERT INTO students (id, name) VALUES ('abc', 'Alex Go');

Error:

ERROR: invalid input syntax for type integer

How to avoid it? Watch your data types in the values you insert. If the data comes from a user form, always validate it in your app.

Mistake #5: Problems With Concurrent Access (Data Leaks)

Imagine two users trying to update the same row in a table at the same time. Without proper transaction isolation, you’re likely to get conflicts.

-- User A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- User B:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;

How to avoid it? Use transactions and isolation levels to prevent simultaneous changes to data.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Mistake #6: Data Loss Because of TRUNCATE

TRUNCATE wipes all rows from a table in a single action and is significantly faster than DELETE because it doesn’t write per-row entries to the WAL. In PostgreSQL, TRUNCATE is transaction-safe: if it runs inside BEGIN ... COMMIT, it can be rolled back with ROLLBACK before the commit (PG 17 docs, TRUNCATE). The real catch is different — by default TRUNCATE does not fire row-level ON DELETE triggers and doesn’t record deletions row by row, so any logic that relies on those triggers won’t run.

-- Deletes everything for good:
TRUNCATE TABLE students;

How to avoid it: both options can be rolled back with ROLLBACK, so the choice depends on other requirements. Use DELETE with a WHERE clause when you need to remove only some rows, keep row-level ON DELETE triggers, or use RETURNING. TRUNCATE is the right choice for fast bulk cleanup of an entire table.

BEGIN;
DELETE FROM students WHERE year_of_study = 1;
-- If you change your mind:
ROLLBACK;

Mistake #7: No Transactions for Important Operations

If you’re running a complex operation with several steps and something fails in the middle, your data can end up in an inconsistent state.

-- Step 1: add a student
INSERT INTO students (name, email) VALUES ('Otto Lin', 'otto.lin@email.com');

-- Step 2: enroll them in a course
INSERT INTO enrollments (student_id, course_id) VALUES (LASTVAL(), 101); -- error

How to avoid it? Wrap these operations in a transaction:

BEGIN;

INSERT INTO students (name, email) VALUES ('Ivan Ivanov', 'ivan.ivanov@email.com');
INSERT INTO enrollments (student_id, course_id) VALUES (LASTVAL(), 101);

COMMIT;

If anything goes wrong at any step, you can roll back the changes:

ROLLBACK;

Mistake #8: Accidental Use of NULL

NULL is full of surprises, since it’s not equal to zero or an empty string, and comparisons with it can give unexpected results.

-- This won’t work:
SELECT * FROM students WHERE email = NULL;

How to avoid it? Use IS NULL or IS NOT NULL:

SELECT * FROM students WHERE email IS NULL;

Typical mistakes are inevitable, but if you know how to spot and avoid them, you’ll be able to work with data safely and efficiently. PostgreSQL is a strict but fair guardian of your data, and it’s always ready to throw an error if something goes wrong. Just remember: mistakes aren’t enemies, they’re teachers.