For programming languages, in every program, and in every module, error handling is an important thing. Everything is expected to go wrong once in a while, and therefore it is vital, and of key importance, to handle errors properly and professionally. In PL/pgSQL, you can use EXCEPTION blocks to handle errors. The idea is that if the BEGIN block does something wrong, the EXCEPTION block will take care of it, and handle the problem correctly. Just like many other languages, such as Java, you can react to different types of errors and catch them separately.

In the following example, the code might run into a division-by-zero problem. The goal is to catch this error and react accordingly:

CREATE FUNCTION error_test1(int, int) RETURNS int AS 
$$ 
BEGIN 
  RAISE  NOTICE 'debug message: % / %', $1, $2;  
  BEGIN 
    RETURN $1 / $2;  
  EXCEPTION 
    WHEN division_by_zero THEN 
      RAISE NOTICE 'division by zero  detected: %', sqlerrm; 
    WHEN others THEN 
      RAISE NOTICE 'some other error: %', sqlerrm; 
  END; 
  RAISE  NOTICE 'all errors handled';  
  RETURN 0; 
END; 
$$ LANGUAGE 'plpgsql'; 

The BEGIN block can clearly throw an error because there can be a division by zero. However, the EXCEPTION block catches the error that we are looking at and also takes care of all other potential problems that can unexpectedly pop up.

Technically, this is more or less the same as savepoint, and therefore the error does not cause the entire transaction to fail completely. Only the block that is causing the error will be subject to a mini rollback.

By inspecting the sqlerrm variable, you can also have direct access to the error message itself. Let's run the code:

test=# SELECT error_test1(9, 0); 
NOTICE:  debug message: 9 / 0 
NOTICE:  division by zero  detected: division by zero 
NOTICE:  all errors handled 
 error_test1  
------------- 
      0 
(1 row) 

PostgreSQL catches the exception and shows the message in the EXCEPTION block. It is kind enough to show us the line that is the error. This makes it a whole lot easier to debug and fix the code if it is broken.

In some cases, it also makes sense to raise your own exception. As you might expect, this is quite easy to do:

RAISE unique_violation 
USING MESSAGE = 'Duplicate user  ID: ' || user_id; 

Apart from this, PostgreSQL offers many predefined error codes and exceptions. The following page contains a complete list of these error messages: https://www.postgresql.org/docs/10/static/errcodes-appendix.html.