PostgreSQL - Errors and Messages

---

---

In PostgreSQL, an error occurs when command fails due to missing data, incorrect syntax or database constraints. While message gives useful information, warning or notices about database constraints.

Syntax

Following is the basic syntax of RAISE statement in PostgreSQL −

RAISE level 'message';

Here,

• The level can be NOTICE, WARNING, or EXCEPTION.
• When EXCEPTION stops the execution, it raises an error.

How to Handles Errors?

To handle the error in PostgreSQL, use the EXCEPTION inside a BEGIN and END block.

Example 1

Here, we provide the simple query to understand the RAISE statement.

postgres=# DO $$
postgres$# BEGIN
postgres$#     RAISE NOTICE 'This is a test message!';
postgres$# END $$;
NOTICE:  This is a test message!
DO

The above query obtained the following result −

Example 2

This example provides a relation error when the table name does not exist in the database, and the implementation of the query is given below, which shows some error statements as an output.

DO $$
BEGIN
    BEGIN
        INSERT INTO users (id, email) VALUES (1, 'user@example.com');
    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE 'Email already exists!';
    END;
END $$;

Following is the output −

Common errors in PostgreSQL

In PostgreSQL, common errors include incorrect syntax, connection failures, missing columns or tables, and authentication issues. To understand these errors in detail, we provide three examples to understand the errors in detail.

Division by Zero

The error occurs when we try to divide a number by zero.

SELECT 10 / 0;

The above query produces the following result −

Table Does Not Exist

This type of error occurs when we try to access the table and it doesn't exist in the database.

SELECT * FROM Products;

The above query produces the following result −

Duplicate Key Value

When we try to insert the duplicate value into a column with UNIQUE constraints, it gives error.

-- Create the table
CREATE TABLE ABC (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE
);
-- Insert values into table
INSERT INTO ABC (email) VALUES ('abc@example.com');
INSERT INTO ABC (email) VALUES ('abc@example.com'); 

The above query obtained the following output −

Key role of Debugging

Following are given some points to understand the key role of debugging −

• It improves the software quality.
• It saves time and resources.
• Debugging is used to identify errors such as syntax errors, logical errors, runtime errors, and semantic errors./li>
• The debugging process allows the developer to fix the error.
• It enhances the software's performance.

Difference between Errors and Messages

Here, you can learn the differences between errors and messages in PostgreSQL −