Exceptions are the unexpected issues that we encounter when executing a program code and it disturbs the functioning of a program. Exceptions can take place due to many reasons including; device failure, wrong user input, insufficient memory for execution of a program or an application, error in code, device failure, division by zero and returning undefined values, network connection issues, etc.

To effectively handle these issues exception handling is done so that the program runs normally and the flow of the system is not disturbed. Exception handling basically prevents the program or system from crashing.

How to Handle Exceptions in PostgreSQL?

In PostgreSQL, exceptions are handled using PL/pgSQL, which is a procedural language. PL/pgSQL provides thorough mechanisms to catch and handle the exceptions that are likely to be encountered in the database during the execution.

When an exception occurs in a block, PostgreSQL terminates the execution of the block and its related transition.

Note: Block is basically a collection of program statements, which are written in a BEGIN-END block structure in PL/pgSQL.

The basic syntax for exception handling in PostgreSQL is:

BEGIN
-- Write your Code here
EXCEPTION
WHEN exception_type1 THEN
-- Write code for Exception handling 
WHEN exception_type2 THEN
-- Code for second Exception handling
END;

In the above code:

• The BEGIN and END indicate the opening and closing of the block.
• Within the BEGIN and END blocks, we can write our code/program.
• If we want to handle exceptions, we will have to add another section in the block. This section is indicated by the EXCEPTION keyword.
• After writing this keyword, we will have to specify the situation with the WHEN keyword which extends the exception type.
• WHEN keyword is followed by a THEN keyword which will include the exception handling code.
• The EXCEPTION keyword will work as if we encounter a specific type of exception during the execution, then the program will handle it in this way(specified in the exception handling code).
• This thing repeats for all the possible exceptions that are likely to interrupt our execution. And we have discussed the exception types above.

Let's move toward the example, which will make the concept of exception handling more clear.

Example: Understanding Exception Handling in Postgres

We will take an example where the program is heading towards the division by zero situation. Let’s consider the following code to understand how exceptions are handled in Postgres:

DO
   $$
 DECLARE 
  exp_variable int;
 BEGIN
  SELECT 1/0 INTO exp_variable;
 END;
   $$
   language plpgsql;

A variable exp_variable is declared under the DECLARE statement. Under the BEGIN clause, we are basically selecting 1/0 and assigning it to the result variable. The output for the given query will be:

So the above query returns an exception. We need to handle this exception. For that, we will add an EXCEPTION statement to it, in order to handle it effectively.

DO
   $$
 DECLARE 
  exp_variable int;
 BEGIN
  SELECT 1/0 INTO exp_variable;
 --   exception example based on SQL ERROR CODE
  EXCEPTION
  WHEN division_by_zero THEN
  exp_variable := NULL;
  RAISE NOTICE 'Exception   has been handled';
 END;
   $$
   language plpgsql;

This will result in:

Similarly, we can write handling code for as many exceptions that are expected to occur and can terminate the normal functioning of our program.

Conclusion

Exceptions are unexpected issues that we encounter while executing a program code. These disturb and sometimes abort the normal functioning of the program. That's why we need to cater to these exceptions and handle them properly. For that, we write some exception-handling code under the keyword “EXCEPTION”. We write the exception-handling code for all the possible exceptions that are likely to interrupt the execution.