Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
To err is human. It implies that mistakes are common among people.
For machines, the above statement is not applicable. Computers cannot ignore errors. Whenever we add invalid or wrong commands, the execution terminates.
So, what’s new in this article? This article will discuss Error Handling in Postgresql.
Before knowing exception handling, it is essential to know about exceptions. An exception is an unpredicted problem that arises during the program's execution. To deal with this unexpected problem, exception handling is used. We perform exception handling to maintain the normal flow of the program.
Let us now start with understanding error handling in PostgreSQL.
Error Handling
In computing and computer programming, handling exceptions is reacting to anomalous or exceptional circumstances that arise during the execution of a program. The details of how this is done depending on whether the exception is a hardware or software one and how the software exception is implemented.
Generally, an exception interrupts the normal flow of execution. Error handling responds to unexpected or unwanted events when a computer program executes. Error handling mainly deals with these events to avoid crashing programs or systems. With this process, exceptions would be able to maintain the normal flow of a program.
Exceptions occur for many reasons, including invalid user input, code errors, device failure, insufficient memory to run an application, a memory conflict with another program, the loss of a network connection, a program attempting to divide by zero, etc.
How to handle the exception?
When a block encounters an error, PostgreSQL will stop both the block's execution and the surrounding transaction.
Syntax
select version() as postgresql_version
$Body$
declare
begin
-- statements;
exception
when condition [or condition...] then
handle_exception;
[when others then
handle_other_exceptions;
]
end;
$Body$
Explanation
How does it work?
First, if an error occurs between the begin and exception, PL/pgSQL halts execution and moves control to the exception list.
Second, PL/pgSQL looks for the first condition that matches the error.
Third, if a match is found, the corresponding handle exception statements are executed. After the end keyword, PL/pgSQL passes control to the statement.
If no match is found, the error propagates until it is finally caught by the exception clause of the block surrounding it. PL/pgSQL will stop processing if no enclosing block exists with the exception clause.
Time for an Example
do
$BODY$
DECLARE
-- Variables
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;
BEGIN
--Statement
DROP TABLE IF exists demo_test.usr_dtls;
CREATE TABLE demo_test.usr_dtls (u_name VARCHAR) ; --Working fine
CREATE TABLE demo_test.usr_ _dtls (u_name VARCHAR) ; -- Error as the same table created above
exception when others then
get stacked diagnostics
v_state = returned_sqlstate,
v_msg = message_text,
v_detail = pg_exception_detail,
v_hint = pg-exception_hint,
v_context = pg_exception_context;
raise notice E' Got exception:
state: %
message: %
detail: %
hint: %
context: %', v_state, v_msg, v_detail, v_hint, v_context;
END;
$BODY$
language plpgsql;
The Declaration block is to define the variables. Before referencing any identifiers in the plpgsql block, they must all be declared in the declaration section. You can give a variable an initial value (as shown in the example below). It is not necessary to give a variable a value before declaring it.
Here, we have defined some variables related to error handling.
We will later assign the predefined values to them in the exception block section.
BEGIN
--Statement
DROP TABLE IF exists demo_test.usr_dtls;
CREATE TABLE demo_test.usr_dtls (u_name VARCHAR) ; --Working fine
CREATE TABLE demo_test.usr_dtls (u_name VARCHAR) ; -- Error as the same table created above
Explanation:
BEGIN block defines the statements to be executed. For example: Create a table, Delete a table, Modify the column name, etc.
In the above example, we have applied a command to DROP the table if the table already exists. Next, we applied a command to create the table: demo_test, which worked fine.
Next, we again applied the same command as above will result in an error.
exception when others then
get stacked diagnostics
v_state = returned_sqlstate,
v_msg = message_text,
v_detail = pg_exception_detail,
v_hint = pg_exception_hint,
v_context = pg_exception_context;
Explanation:
exception when others then: The cursor will enter into this block if any exception occurs in the statements mentioned in the BEGIN block.
get stacked diagnostics: In this block, we assign the variables the predefined value generated by plpgsql.
Let us understand them:
returned_sqlstate: It defines the state of the exception.
message_text: It defines the type of exception. Eg: Table already exists.
pg_exception_detail: It defines the detail of the exception.
pg_exception_hint: Generally, PostgreSQL hints at the type of exception that might occur. For example, if the table xyz already exists, it will give you a hint to use another name for the new table.
pg_exception_context: Context provides the line number at which the error occurs.
raise notice E' Got exception: It is used to print the exception on the console.
Now, let us run the above code to see the output.
Output
NOTICE:
Got exception:
state : 42P07
message: relation "us_dtls" already exists detail:
hint
context: SQL statement "CREATE TABLE demo_test.us_dtls (u_name VARCHAR)"
PL/pgSQL function inline_code_block line 13 at SQL statement
DO
Query returned successfully in 64 msec.
Frequently asked questions
What is the precondition for creating a database in PostgreSQL?
To create a database in PostgreSQL, the user must be a superuser or have the appropriate privileges to create a database.
What are the ways of creating a database in PostgreSQL?
There are two ways of creating a database in PostgreSQL. These are:
Using CREATE DATABASE command on PostgreSQL shell prompt.
Using createdb command on command prompt.
How can we see our databases in PostgreSQL?
We can see our databases in PostgreSQL using the \l (backslash l) command. \l or \list is a psql tool that lists all the databases in the current server.
Conclusion
In this article, we talked about Error Handling in Postgresql. We understood both ways through their syntax and examples.
Attention Reader!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.