Handling Errors When Inserting and Updating Data

Let's break down the typical mistakes when inserting new data into a table.

Error 1: Trying to insert NULL into a required field

PostgreSQL keeps a close eye to make sure your database rules are followed. Here are some examples of constraints that can cause errors:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL, -- Name can't be NULL
    age INT
);

-- Error: the name field is required
INSERT INTO students (name, age) VALUES (NULL, 20);

Result: error null value in column "name" of relation "students" violates not-null constraint`.

You gotta keep an eye on what data you're adding. Maybe this column used to allow NULLs, but now it's required.

Error 2: Duplicate data in a unique column.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT UNIQUE -- Course name must be unique
);

-- First insert is successful
INSERT INTO courses (course_name) VALUES ('SQL Basics');

-- Second insert causes an error
INSERT INTO courses (course_name) VALUES ('SQL Basics');

Result: error duplicate key value violates unique constraint`.

Usually this isn't your fault, it's just the user accidentally trying to do something twice. You don't really need to do anything in this situation.

Error 3: Breaking referential integrity.

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(id), -- There must be a student with this ID
    course_id INT REFERENCES courses(course_id)
);

-- Error: student with ID = 99 doesn't exist
INSERT INTO enrollments (student_id, course_id) VALUES (99, 1);

Result: error insert or update on table "enrollments" violates foreign key constraint`.

Honestly, it's a good thing this error pops up. There's nothing worse than breaking your database's integrity. Most likely, there's a bug in the code working with the database, or maybe some data is outdated. Either way, if the database doesn't let you break its integrity—that's awesome.

Error Handling in PostgreSQL

Yeah, errors happen. But it's not just about noticing them, you gotta know how to deal with them.

Transactions as a Safety Tool

When working with data, we often use transactions to keep things consistent. If something goes wrong, we can roll back the changes.

Example: adding data to two tables.

BEGIN; -- Start the transaction

-- Insert data into the students table
INSERT INTO students (name, age) VALUES ('Otto Lin', 21);

-- Insert a record into the enrollments table
-- This will throw an error if there's no course with ID=10
INSERT INTO enrollments (student_id, course_id) VALUES (1, 10);

-- If everything went fine
COMMIT;
-- If there was an error, "roll back" the changes
ROLLBACK;

If the course with course_id = 10 doesn't exist, the insert into the students table will also be rolled back.

Error Handling in Transactions

In PostgreSQL, you can actually predict errors and handle them right in your queries using EXCEPTION blocks.

Example: we're adding a student and enrolling them in a course. If there's an error, a log entry about the error is added.

DO $$
BEGIN
    -- Trying to insert data
    INSERT INTO students (name, age) VALUES ('Anna Song', 22);
    INSERT INTO enrollments (student_id, course_id) VALUES (2, 999); -- Error

    -- If everything is successful
    RAISE NOTICE 'Record added successfully!';
EXCEPTION
    WHEN foreign_key_violation THEN
        -- Handle foreign key violation
        RAISE WARNING 'Course with the specified course_id does not exist.';
END $$;

Checking Uniqueness with ON CONFLICT

You can prevent errors related to UNIQUE constraint violations ahead of time using the ON CONFLICT construct. This lets you specify what to do in case of a conflict.

Example: if you try to insert a duplicate course, just skip the insert.

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO NOTHING; -- Skip duplicate data

Or update the existing row:

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO UPDATE     
    SET course_name = EXCLUDED.course_name || ' (Updated)';

I'll tell you more about the ON CONFLICT operator in the next level, when we get into bulk data loading :P

Common Data Handling Mistakes and How to Prevent Them

You've already seen that the main sources of errors are:

1. Breaking constraints (NOT NULL, UNIQUE, FOREIGN KEY).
2. Missing conditions in queries (WHERE) when updating or deleting data.
3. Mistakes in transaction order.

To keep yourself safe:

• Use transactions and ROLLBACK for big operations.
• Always check your data before inserting.
• Log errors for analysis.
• Use ON CONFLICT to avoid duplicate records.

Now you're armed with the knowledge to fight errors! Remember: a good developer isn't someone who never makes mistakes, but someone who knows how to fix them.

2

Task

SQL SELF, level 22, lesson 3

Locked

Using the `ON CONFLICT` Clause to Prevent Errors

Using the `ON CONFLICT` Clause to Prevent Errors

Open