PostgreSQL Conditions
2021-06-16 12:47:16

PostgreSQL Conditions

PostgreSQL Conditions

PostgreSQL Conditions are used as filters to extract only the desired and meaningful data from the PostgreSQL database table. PostgreSQL Conditions help us to save our time because they will display on those data that are more relevant to the user according to the query added by the user.

There are various types of conditions supported by PostgreSQL, some of the major conditions are :

  • AND Condition
  • OR Condition
  • AND & OR Condition
  • NOT Condition
  • LIKE Condition
  • IN Condition
  • NOT IN Condition
  • BETWEEN Condition
  • EXIST Condition

Now let us see these PostgreSQL Conditions one-by-one and see their usage and working.

AND Condition:

The PostgreSQL AND Condition are usually used with the UPDATE, INSERT, SELECT, and DELETE commands.  The AND Condition will display data only when the condition specified by the  AND condition which means we can use AND condition as an additional feature for displaying only relevant data to the user.

 The AND condition has syntax like:

 WHERE conditionONE
 AND conditionTWO
 ...   
 AND condition_N;   


So let us understand the working of AND condition with the help of an example.

PostgreSQL Conditions

As shown in the image we have created a table named ‘employee’ having 3 columns named name, age, and salary and we have added 5 rows in the table.

Let us apply AND condition on this table and observe the output.

PostgreSQL Conditions

As shown in the image the AND condition is used to check two conditions where the first condition is that the age should be greater than 22 years and the salary of the employee to be less than 75.

And there is only one row in the table that satisfies both the conditions and that row is shown as the output of the query.

PostgreSQL OR Condition:

The PostgreSQL OR Condition are usually used with the UPDATE, INSERT, SELECT, and DELETE commands. This condition works similarly to AND condition. If row satisfies any of the conditions written in the OR condition querythen that row will be displayed as output.

The syntax for the OR condition is,

 WHERE conditionONE
 OR conditionTWO
 ...   
 OR condition_n;   

Let us consider an example to understand the OR condition.

PostgreSQL Conditions

As shown in the figure all the rows satisfy one of the conditions that are either age is greater than 22 or the salary is less than 75 that is why all the rows are present in the output of the query.

PostgreSQL Not Condition:

The PostgreSQL NOT Condition is usually used with the UPDATE, INSERT, SELECT, and DELETE commands. This condition is used to negate a condition in a command.

The NOT condition has syntax like:

NOT Condition_to_be_specified
PostgreSQL Conditions

The above example shows the usage of NOT condition in PostgreSQL. As we can see in the image only those rows whose age is not between 23 and 29 are displayed so actually the NOT condition negated the BETWEEN condition that is getting the rows having age between 23 and 29.

In a similar fashion, we can use not condition with other conditions also.

PostgreSQL Like condition:

The PostgreSQL LIKE condition is used to display those rows from the table where the LIKE condition is satisfied. The results obtained from this condition are case sensitive and follow a specific pattern.

 The syntax for the PostgreSQL LIKE condition is:

 expression_of_query  LIKE pattern [ ESCAPE 'escaping_character' ]   
 The NOT operator can also be used with the LIKE operator as follows:
 expression_of_query NOT LIKE pattern[ ESCAPE 'escaping_character' ]   

 Let us understand the LIKE condition with the help of an example.

PostgreSQL Conditions

As shown in the image only those rows whose names start with the letter A are displayed.

PostgreSQL Conditions

Another usage of the LIKE condition is shown in the above image where only those rows are displayed where the starting name starts with the letter A and ends with the letter W.

PostgreSQL Between Condition:

The PostgreSQL Between condition is used to retrieve values from an expression within the specified range. In other words, the Between condition is used to match a value against a range of values, and only those rows are displayed in the output of the query.

The PostgreSQL Between condition is also known as PostgreSQL Between Operator.

PostgreSQL Conditions

As shown in the image, only those rows are displayed where salary is between the specified range and the condition that we had specified for this query is that the salary should be between 50 and 90. And there are only two rows that satisfy this particular BETWEEN condition.

So, with the help of this article, we are able to understand various PostgreSQL conditions and their usage.