PostgreSQL - Errors & Messages

PostgreSQL - Errors and Messages

In PostgreSQL, understanding how to handle errors and generate messages is crucial for building robust applications and for effective debugging. Errors are conditions that halt the current transaction, while messages provide non-fatal information, warnings, or debugging output.

Common SQL Errors

Before handling errors programmatically, it’s important to recognize common errors that arise from direct SQL queries. Here are a few examples:

1. Syntax Error (sqlstate: 42601)

This occurs when a query violates SQL’s grammatical rules.

postgres=# SELEC * FROM employees;
ERROR:  42601: syntax error at or near "SELEC"
LINE 1: SELEC * FROM employees;
        ^

2. Undefined Table (sqlstate: 42P01)

This error happens when you try to query a table that does not exist.

postgres=# SELECT * FROM products;
ERROR:  42P01: relation "products" does not exist
LINE 1: SELECT * FROM products;
                      ^

3. Unique Violation (sqlstate: 23505)

This occurs when you try to insert a value that already exists in a column with a UNIQUE or PRIMARY KEY constraint.

-- Assuming 'employees' table exists with a PRIMARY KEY on 'id'
postgres=# INSERT INTO employees (id, first_name) VALUES (1, 'Peter');
ERROR:  23505: duplicate key value violates unique constraint "employees_pkey"
DETAIL:  Key (id)=(1) already exists.

4. Division by Zero (sqlstate: 22012)

Attempting to divide a number by zero results in this mathematical error.

postgres=# SELECT 100 / 0;
ERROR:  22012: division by zero

Handling Errors with PL/pgSQL

For more complex logic inside functions or procedures, you can gracefully handle errors using a BEGIN...EXCEPTION...END block. This prevents your entire function from failing if a predictable error occurs.

Syntax

DO $$
BEGIN
    -- code that might cause an error
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        -- handler code for the specified error condition
    WHEN OTHERS THEN
        -- handler for any other errors
END;
$$;

The condition can be a specific SQLSTATE code (e.g., unique_violation for ‘23505’).

Example: Handling a Unique Violation

Let’s create a function to add a new employee, but instead of failing on a duplicate email, it will raise a notice.

-- First, add a unique constraint on email
ALTER TABLE employees ADD COLUMN email VARCHAR(100) UNIQUE;
INSERT INTO employees (id, first_name, email) VALUES (10, 'Test', 'test@example.com');

-- Now, the DO block with error handling
DO $$
BEGIN
    INSERT INTO employees (id, first_name, email) VALUES (11, 'Another', 'test@example.com');
    RAISE NOTICE 'Employee added successfully!';
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'The email ''test@example.com'' already exists. No new employee was added.';
END;
$$;

Running this block will not produce an error. Instead, it will output our custom notice:

NOTICE:  The email 'test@example.com' already exists. No new employee was added.
DO

Generating Messages with RAISE

The RAISE statement is used to report messages and throw errors within PL/pgSQL code. It’s an invaluable tool for debugging and communicating state.

Syntax

RAISE level 'format' [, expression ...];

Common levels include:

• NOTICE: For informational messages that don’t stop execution. Useful for debugging.
• WARNING: For warnings about potential issues.
• EXCEPTION: This is the most critical level. It raises an error that aborts the current transaction (unless caught by an EXCEPTION block).

Example: Using RAISE for Validation

Let’s create a function that updates an employee’s salary but throws a custom exception if the new salary is lower than the old one.

CREATE OR REPLACE FUNCTION update_salary(emp_id INT, new_salary NUMERIC)
RETURNS void AS $$
DECLARE
    current_salary NUMERIC;
BEGIN
    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;

    IF new_salary < current_salary THEN
        RAISE EXCEPTION 'Salary decrease is not allowed. Current: %, New: %', current_salary, new_salary;
    END IF;

    UPDATE employees SET salary = new_salary WHERE id = emp_id;
    RAISE NOTICE 'Salary for employee % updated successfully.', emp_id;
END;
$$ LANGUAGE plpgsql;

-- Try to perform a forbidden update
SELECT update_salary(1, 40000.00);

This call will fail and produce our custom error message:

ERROR:  Salary decrease is not allowed. Current: 55000.00, New: 40000.00
CONTEXT:  PL/pgSQL function update_salary(integer,numeric) line 8 at RAISE

Debugging Strategies

• Use RAISE NOTICE Liberally: Sprinkle RAISE NOTICE 'Reached point A with value: %', my_variable; inside complex functions to trace execution flow and inspect variable states.
• Check client_min_messages: If you don’t see your NOTICE messages, your client might be configured to ignore them. Run SHOW client_min_messages; and set it to notice if needed (SET client_min_messages = 'notice';).
• Isolate the Query: If a function fails, extract the queries from it and run them manually with hardcoded values to see which one is causing the error.
• Read the Error Carefully: PostgreSQL error messages are very informative. They usually include a hint and point to the exact line and character where the error occurred.