PostgreSQL Exception

Summary: in this tutorial, you will learn how to catch PostgreSQL exceptions in PL/pgSQL.

Introduction to the PL/pgSQL Exception clause

When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction.

To recover from the error, you can use the exception clause in the begin...end block.

The following illustrates the syntax of the exception clause:

<<label>>
declare
begin
    statements;
exception
    when condition [or condition...] then
       handle_exception;
   [when condition [or condition...] then
       handle_exception;]
   [when others then
       handle_other_exceptions;
   ]
end;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

• First, when an error occurs between the begin and exception, PL/pgSQL stops the execution and passes the control to the exception list.
• Second, PL/pgSQL searches for the first condition that matches the occurring error.
• Third, if there is a match, the corresponding handle_exception statements will execute. PL/pgSQL passes the control to the statement after the end keyword.
• Finally, if no match found, the error propagates out and can be caught by the exception clause of the enclosing block. In case there is no enclosing block with the exception clause, PL/pgSQL will abort the processing.

The condition names can be no_data_found in case of a select statement return no rows or too_many_rows if the select statement returns more than one row. For a complete list of condition names on the PostgreSQL website.

It’s also possible to specify the error condition by SQLSTATE code. For example, P0002 for no_data_found and P0003 for too_many_rows.

Typically, you will catch a specific exception and handle it accordingly. To handle other exceptions rather than the one you specify on the list, you can use the when others then clause.

Handling exception examples

We’ll use the film table from the sample database for the demonstration.

1) Handling no_data_found exception example

The following example issues an error because the film with id 2000 does not exist.

do
$$
declare
	rec record;
	v_film_id int = 2000;
begin
	-- select a film 
	select film_id, title 
	into strict rec
	from film
	where film_id = v_film_id;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL statement
SQL state: P0002
Code language: Shell Session (shell)

The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message:

do
$$
declare
	rec record;
	v_film_id int = 2000;
begin
	-- select a film 
	select film_id, title 
	into strict rec
	from film
	where film_id = v_film_id;
        -- catch exception
	exception 
	   when no_data_found then 
	      raise exception 'film % not found', v_film_id;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

ERROR:  film 2000 not found
CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
SQL state: P0001
Code language: Shell Session (shell)

2) Handling too_many_rows exception example

The following example illustrates how to handle the too_many_rows exception:

do
$$
declare
	rec record;
begin
	-- select film 
	select film_id, title 
	into strict rec
	from film
	where title LIKE 'A%';
	
	exception 
	   when too_many_rows then
	      raise exception 'Search query returns too many rows';
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

ERROR:  Search query returns too many rows
CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
SQL state: P0001
Code language: Shell Session (shell)

In this example, the too_many_rows exception occurs because the select into statement returns more than one row while it is supposed to return one row.

3) Handling multiple exceptions

The following example illustrates how to catch multiple exceptions:

do
$$
declare
	rec record;
	v_length int = 90;
begin
	-- select a film 
	select film_id, title 
	into strict rec
	from film
	where length = v_length;
	
        -- catch exception
	exception 
	   when sqlstate 'P0002' then 
	      raise exception 'film with length % not found', v_length;
	   when sqlstate 'P0003' then 
	      raise exception 'The with length % is not unique', v_length;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

ERROR:  The with length 90 is not unique
CONTEXT:  PL/pgSQL function inline_code_block line 17 at RAISE
SQL state: P0001
Code language: Shell Session (shell)

4) Handling exceptions as SQLSTATE codes

The following example is the same as the one above except that it uses the SQLSTATE codes instead of the condition names:

do
$$
declare
	rec record;
	v_length int = 30;
begin
	-- select a film 
	select film_id, title 
	into strict rec
	from film
	where length = v_length;
	
        -- catch exception
	exception 
	   when sqlstate 'P0002' then 
	      raise exception 'film with length % not found', v_length;
	   when sqlstate 'P0003' then 
	      raise exception 'The with length % is not unique', v_length;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

ERROR:  film with length 30 not found
CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
SQL state: P0001
Code language: Shell Session (shell)

Summary

• Use the exception clause in the begin...end block to catch and handle exceptions.