close
The Wayback Machine - https://web.archive.org/web/20240930231553/https://www.geeksforgeeks.org/postgresql-assert/
Open In App

PostgreSQL – Assert

Last Updated : 05 Aug, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

PostgreSQL provides the ASSERT statement as a vital tool for inserting debugging checks in PL/pgSQL code. This statement is crucial for identifying logical errors, making it easier to catch problems in your code early on.

Let us better understand the Assert Statement in PostgreSQL from this article.

BERJAYA

Syntax

assert condition [, message];  

Parameters

If we analyze the above syntax:

  • condition: The condition is a boolean expression that should return true.
    • If the condition returns true, the assert statement does nothing, and the program continues to run.
    • If the condition is false or null, an ‘ASSERT_FAILURE’ exception is raised.
    • Any error encountered while evaluating the condition is reported as a normal error.
       
  • message: The message is an optional part of the ASSERT statement.
    • It is an expression whose result (if not null) replaces the default error message text “assertion failed” should the condition fail.
    • The message expression is only evaluated if the assertion fails, not when it succeeds.

It is important to note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. In the case of reporting bugs, one must use the RAISE keyword.

PostgreSQL Assert Statement

Let us take a look at some of the examples of the Assert Statement in PostgreSQL to better understand the concept.

First, we create a sample table using the below commands to perform examples:

PostgreSQL
CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  full_name VARCHAR NOT NULL,
  manager_id INT
);
INSERT INTO employees (
  employee_id,
  full_name,
  manager_id
)
VALUES
  (1, 'M.S Dhoni', NULL),
  (2, 'Sachin Tendulkar', 1),
  (3, 'R. Sharma', 1),
  (4, 'S. Raina', 1),
  (5, 'B. Kumar', 1),
  (6, 'Y. Singh', 2),
  (7, 'Virender Sehwag ', 2),
  (8, 'Ajinkya Rahane', 2),
  (9, 'Shikhar Dhawan', 2),
  (10, 'Mohammed Shami', 3),
  (11, 'Shreyas Iyer', 3),
  (12, 'Mayank Agarwal', 3),
  (13, 'K. L. Rahul', 3),
  (14, 'Hardik Pandya', 4),
  (15, 'Dinesh Karthik', 4),
  (16, 'Jasprit Bumrah', 7),
  (17, 'Kuldeep Yadav', 7),
  (18, 'Yuzvendra Chahal', 8),
  (19, 'Rishabh Pant', 8),
  (20, 'Sanju Samson', 8);
SELECT * FROM employees;

Output:

BERJAYA

Example 1: Checking for Records in the Table

The below query is used to check whether our table has one or more than 1 record.

Query:

DO $$
DECLARE  
  employee_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO employee_count
  FROM employees;
  
  ASSERT employee_count > 0, 'Check for Empty Employee Table';
END $$;

Output:

BERJAYA

Explanation: As the size of the table is greater than 0, the assertion is true and hence, nothing is printed as the error message.

Example 2: Checking for More Than 100 Records

The below query is used to check whether our table has one or more than 100 records.

Query:

DO $$
DECLARE  
  employee_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO employee_count
  FROM employees;
  
  ASSERT employee_count > 100, '100 employee count found';
END $$;

Output:

BERJAYA

Explanation: As the table has fewer than 100 records, the assertion fails, and the error message “100 employee count found” is printed.

Important Points About the PostgreSQL ASSERT Statement

  • The ASSERT statement is designed to detect program bugs, not for regular error handling.
  • Always use ASSERT with a boolean condition to check for expected logical states in your code.
  • Assertions are only evaluated when the condition fails, ensuring minimal performance impact during normal execution.
  • Use RAISE for handling known error conditions instead of ASSERT.


Previous Article
Next Article

Similar Reads

PostgreSQL - Connect To PostgreSQL Database Server in Python
The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python setup.py build sudo python setup.py installCreate a
4 min read
PostgreSQL - Export PostgreSQL Table to CSV file
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER; Note: If you have permissio
2 min read
PostgreSQL - Installing PostgreSQL Without Admin Rights on Windows
If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine. Installation: Follow the be
3 min read
PostgreSQL - Creating Updatable Views Using WITH CHECK OPTION Clause
PostgreSQL is the most advanced general purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop application or as a web in your browser. In this article, we
4 min read
What is PostgreSQL - Introduction
This is an introductory article for the PostgreSQL database management system. In this we will look into the features of PostgreSQL and why it stands out among other relational database management systems. Brief History of PostgreSQL: PostgreSQL also known as Postgres, was developed by Michael Stonebraker of the University of California, Berkley. I
2 min read
Install PostgreSQL on Mac
This is a step-by-step guide to install PostgreSQL on a Mac OS machine. We will be installing PostgreSQL version 11.3 on Mac using the installer provided by EnterpriseDB in this article. There are three crucial steps for the installation of PostgreSQL as follows: Download PostgreSQL EnterpriseDB installer for MacInstall PostgreSQLVerify the install
2 min read
PostgreSQL - Loading a Database
In this article we will look into the process of loading a PostgreSQL database into the PostgreSQL database server. Before moving forward we just need to make sure of two things: PostgreSQL database server is installed on your system. A sample database. For the purpose of this article, we will be using a sample database which is DVD rental database
3 min read
PostgreSQL - NOT LIKE operator
The PostgreSQL NOT LIKE works exactly opposite to that of the LIKE operator. It is used to data using pattern matching techniques that explicitly excludes the mentioned pattern from the query result set.Its result include strings that are case-sensitive and doesn't follow the mentioned pattern. It is important to know that PostgreSQL provides with
2 min read
PostgreSQL - LIKE operator
The PostgreSQL LIKE operator is used query data using pattern matching techniques. Its result include strings that are case-sensitive and follow the mentioned pattern. It is important to know that PostgreSQL provides with 2 special wildcard characters for the purpose of patterns matching as below: Percent ( %) for matching any sequence of character
2 min read
PostgreSQL - SELF JOIN
PostgreSQL has a special type of join called the SELF JOIN which is used to join a table with itself. It comes in handy when comparing the column of rows within the same table. As, using the same table name for comparison is not allowed in PostgreSQL, we use aliases to set different names of the same table during self-join. It is also important to
3 min read
Install PostgreSQL on Linux
This is a step-by-step guide to install PostgreSQL on a Linux machine. By default, PostgreSQL is available in all Ubuntu versions as PostgreSql "Snapshot". However other versions of the same can be downloaded through the PostgreSQL apt repository. We will be installing PostgreSQL version 11.3 on Ubuntu in this article. There are three crucial steps
2 min read
PostgreSQL - Rename Database
In PostgreSQL, the ALTER DATABASE RENAME TO statement is used to rename a database. The below steps need to be followed while renaming a database: Disconnect from the database that you want to rename by connecting to a different database.Terminate all connections, connected to the database to be renamed.Now you can use the ALTER DATABASE statement
1 min read
PostgreSQL - Size of tablespace
In this article, we will look into the function that is used to get the size of the PostgreSQL database tablespace. The pg_tablespace_size() function is used to get the size of a tablespace of a table. This function accepts a tablespace name and returns the size in bytes. Syntax: select pg_tablespace_size('tablespace_name'); Example 1: Here we will
2 min read
PostgreSQL - Removing Temporary Table
In PostgreSQL, one can drop a temporary table by the use of the DROP TABLE statement. Syntax: DROP TABLE temp_table_name; Unlike the CREATE TABLE statement, the DROP TABLE statement does not have the TEMP or TEMPORARY keyword created specifically for temporary tables. To demonstrate the process of dropping a temporary table let's first create one b
2 min read
PostgreSQL - Block Structure
PL/pgSQL is a block-structured language, therefore, a PL/pgSQL function or store procedure is organized into blocks. Syntax: [ <<label>> ] [ DECLARE declarations ] BEGIN statements; ... END [ label ]; Let's analyze the above syntax: Each block has two sections: declaration and body. The declaration section is optional while the body sec
4 min read
Introduction to PostgreSQL PL/pgSQL
In this, we will discuss the overview of PostgreSQL PL/pgSQL and will also cover the CRUD(CREATE, READ, UPDATE, DELETE) operations with the help of the example of each operation and finally will discuss the advantages and disadvantages of PostgreSQL PL/pgSQL. Let's discuss it one by one.PostgreSQL :It is a powerful, open-source object-relational da
4 min read
PostgreSQL - IF Statement
PostgreSQL has an IF statement executes `statements` if a condition is true. If the condition evaluates to false, the control is passed to the next statement after the END IF part. Syntax: IF condition THEN statements; END IF; The above conditional statement is a boolean expression that evaluates to either true or false. Example 1: In this example,
2 min read
PostgreSQL - Introduction to Stored Procedures
PostgreSQL allows the users to extend the database functionality with the help of user-defined functions and stored procedures through various procedural language elements, which are often referred to as stored procedures. The store procedures define functions for creating triggers or custom aggregate functions. In addition, stored procedures also
3 min read
PostgreSQL - Select Into
In PostgreSQL, the select into statement to select data from the database and assign it to a variable. Syntax: select select_list into variable_name from table_expression; In this syntax, one can place the variable after the into keyword. The select into statement will assign the data returned by the select clause to the variable. Besides selecting
2 min read
PostgreSQL - Reset Password For Postgres
In this article, we will look into the step-by-step process of resetting the Postgres user password in case the user forgets it. PostgreSQL uses the pg_hba.conf configuration file stored in the database data directory (e.g., C:\Program Files\PostgreSQL\12\data on Windows) and is used to handle user authentication. The hba in pg_hba.conf means host-
2 min read
PostgreSQL - Show Databases
In PostgreSQL, there are couple of ways to list all the databases present on the server. In this article, we will explore them. Using the pSQL command: To list all the database present in the current database server use one of the following commands: Syntax: \l or \l+ Example: First log into the PostgreSQL server using the pSQL shell: Now use the b
1 min read
PostgreSQL - CONCAT_WS Function
In PostgreSQL, the CONCAT_WS function is used to concatenate strings with a separator. Like the CONCAT function, the CONCAT_WS function is also variadic and ignored NULL values. The following illustrates the syntax of the CONCAT_WS function. Syntax: CONCAT_WS(separator, string_1, string_2, ...); Let's analyze the above syntax: The separator is a st
1 min read
PostgreSQL - Connecting to the database using Python
PostgreSQL API for Python allows user to interact with the PostgreSQL database using the psycopg2 module. In this article we will look into the process of connecting to a PostgreSQL database using Python. Prerequisites: First we will need to install the psycopg2 module with the below command in the command prompt: pip install psycopg2 Creating a Da
2 min read
PostgreSQL - Insert Data Into a Table using Python
In this article we will look into the process of inserting data into a PostgreSQL Table using Python. To do so follow the below steps: Step 1: Connect to the PostgreSQL database using the connect() method of psycopg2 module.conn = psycopg2.connect(dsn) Step 2: Create a new cursor object by making a call to the cursor() methodcur = conn.cursor() Ste
2 min read
PostgreSQL - While Loops
PostgreSQL provides the loop statement which simply defines an unconditional loop that executes repeatedly a block of code until terminated by an exit or return statement. The while loop statement executes a block of code till the condition remains true and stops executing when the conditions become false. The syntax of the loop statement: [ <
2 min read
PostgreSQL - For Loops
PostgreSQL provides the for loop statements to iterate over a range of integers or over a result set or over the result set of a dynamic query. The different uses of the for loop in PostgreSQL are described below: 1. For loop to iterate over a range of integers The syntax of the for loop statement to iterate over a range of integers: [ <<labe
4 min read
How to use PostgreSQL Database in Django?
This article revolves around how can you change your default Django SQLite-server to PostgreSQL. PostgreSQL and SQLite are the most widely used RDBMS relational database management systems. They are both open-source and free. There are some major differences that you should be consider when you are choosing a database for your applications. Also ch
2 min read
PostgreSQL - SQL Optimization
PostgreSQL is the most advanced general-purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop application or as a web in your browser. You can download th
5 min read
PostgreSQL CRUD Operations using Java
CRUD (Create, Read, Update, Delete) operations are the basic fundamentals and backbone of any SQL database system. CRUD is frequently used in database and database design cases. It simplifies security control by meeting a variety of access criteria. The CRUD acronym identifies all of the major functions that are inherent to relational databases and
8 min read
How To Connect and run SQL queries to a PostgreSQL database from Python
This article focus on connecting to a PostgreSQL database from Python. Installation:Install PostgreSQL, If you haven't installed it.We need to install the psycopg2 library to connect to a PostgreSQL database. Open the command prompt and run the below command to install psycopg2pip3 install psycopg2Creating a Database You can create a Database in 2
2 min read