Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Error Handling 
3.
How to handle the exception? 
4.
Time for an Example 
5.
Frequently asked questions
5.1.
What is the precondition for creating a database in PostgreSQL?
5.2.
What are the ways of creating a database in PostgreSQL?
5.3.
How can we see our databases in PostgreSQL?
6.
Conclusion 
Last Updated: Mar 27, 2024
Easy

Error Handling in Postgresql

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

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. 

Error Handling in PostgreSQL

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 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. 

Reasons for exception

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. 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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

Error Handling in PostgreSQL

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;


Explanation

Let us understand the code in detail:

DECLARE
-- Variables
    v_state TEXT;
    v_msg TEXT;
    v_detail TEXT;
    v_hint TEXT;
    v_context TEXT;


Explanation:

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:
    state: % 
    message: % 
    detail: %
    hint: %
    context: %',  v_state, v_msg, v_detail, v_hint, v_context;


Explanation:

raise notice E' Got exception: It is used to print the exception on the console. 

Error Handling in PostgreSQL

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.
output

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:

  1. Using CREATE DATABASE command on PostgreSQL shell prompt.
  2. 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.

If you want to explore more about PostgreSQL and other databases, then refer to these links, MongoDBDatabases, and relational databasesnon-relational databases. Also, look at the Coding Ninjas website for some great information, Web DevelopmentCoding Ninjas Studio ProblemsCoding Ninjas Studio Interview BundleCoding Ninjas Studio Interview ExperiencesCoding Ninjas CoursesCoding Ninjas Studio Contests, and Coding Ninjas Studio Test Series

Do upvote our blog to help other ninjas grow.

Happy Coding!

Live masterclass